Take out characters and put in a new column in Excel

Question

Hi I'm a bit new to vba so I will try to explain my problem as far as possible. I have a dataset in Excel in Column A, I have a lot of file names like this:

 1. AB000**1234**45.tif 
 2. AB000**1235**45.tif
 3. AB000**1236**45.tif
 4. AB000**1237**45.tif

etc..

From this I want to take out all the strong characters and put in column C so it will look like this:

etc..

At the moment I have a code that looks like this:

Sub TakeOut
    Dim str1 As String
    Dim LR As Long
    Dim cell As Range, RNG As Range

    LR = Range("A" & Rows.Count).End(xlUp).Row   
    Set RNG = Range("A1:A" & LR)

    For Each cell In RNG
        L = Len(RNG) 
        If L > 0 Then
           RNG = ...
        End If
    Next cell

    Range("C:C").Columns.AutoFit

End Sub

I have tried to count left(5) and right(6) but don't know how to take out the 4 character that I want. Hope you can help me with this.

RNG = mid(Cell,InStrRev(Cell, "1"),4), this will cut the value in cell at the first instance of 1 for four characters. — Lowpar, Jul 18 '16 at 13:01
@JessieQuick, did you check my answer below. Hope you are looking for that method? — Karthick Gunasekaran, Jul 19 '16 at 04:18

score 2 · Answer 1 · answered Jul 18 '16 at 12:58

2

Take a look at the Mid() Function link.

usage in your case:

Mid(cell.Value, 6, 4) 'First parameter is the string, 6 is the start character, 4 is length

answered Jul 18 '16 at 12:58

gizlmo

1,882
1
14
14

Thanks all for the replay and help! I learned alot from this :) – JessieQuick Jul 21 '16 at 10:42

score 2 · Answer 2 · answered Jul 18 '16 at 13:04

2

The easiest way without looping would be something like this:

Sub TakeOut()
  Dim rng As Range
  Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
  rng.Offset(, 1) = Evaluate("IF(" & rng.Address & "="""","""",MID(" & rng.Address & ",6,4))")
End Sub

answered Jul 18 '16 at 13:04

Dirk Reichel

7,989
1
15
31

This was also a nice one! but i don't really know what's happening in ** Evaluate("IF(" & rng.Address & "="""","""", ** this part – JessieQuick Jul 21 '16 at 09:44
This just returns an empty cell if the cell in column A is empty... in general, this returns an array like the array function in the sheet would do. This way you do not need to loop. ;) – Dirk Reichel Jul 21 '16 at 10:15

score 2 · Accepted Answer · answered Jul 18 '16 at 13:44

If you want to take out the strong characters from the string. Try it below. It will take all the Bold Characters in a cell and place it in C column.

Hope you are looking for this?

Sub get_bold_content()
    Dim lastrow, i, j, totlength As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lastrow
        totlength = Len(Range("A" & i).Value)
        For j = 1 To totlength
            If Range("A" & i).Characters(j, 1).Font.Bold = True Then
                outtext = outtext & Range("A" & i).Characters(j, 1).Text
            End If
        Next j
        Range("C" & i).Value = outtext
        outtext = ""
    Next i
End Sub

Thanks for your reply. Happy to help always! – Karthick Gunasekaran Jul 20 '16 at 04:06 — Karthick Gunasekaran, Jul 20 '16 at 04:06

Take out characters and put in a new column in Excel

3 Answers3