Removing Characters in VBA

[zeekiehafa]

Hi all,

I am writing a vba program that needs to take data from an excel table that was converted from a pdf file and then process it for my specific needs. The processing of the data isn't important to my question.

The problem I have is that when this pdf table is converted to an excel table, items that I don't need that should be in separate cells end up being merged into the same cell with number values that I need.

For example, I need to use a number say it's 100,000 that should be in a cell by itself. But in the cell there's this string "100,000 (113)". Every single number has a space and a number between brackets after it. I need to write a VBA macro that gets rid of everything after the number. It needs to take something like this "numberIwant (numberIdon'twant)" and turn it into this "numberIwant" and then convert that to the single data type.

I've been trying and trying to figure it out myself and it's driving me stark raving mad at times. I would appreciate some help.

 

[Dosnox]

Hi zeekiehafa

How about trying

Range("B1").Value = Left(Range("A1"), InStr(1, Range("A1").Value, "(") - 2)

where the original string is in A1

 

[mirabeau]

Hi all,

I am writing a vba program that needs to take data from an excel table that was converted from a pdf file and then process it for my specific needs. The processing of the data isn't important to my question.

The problem I have is that when this pdf table is converted to an excel table, items that I don't need that should be in separate cells end up being merged into the same cell with number values that I need.

For example, I need to use a number say it's 100,000 that should be in a cell by itself. But in the cell there's this string "100,000 (113)". Every single number has a space and a number between brackets after it. I need to write a VBA macro that gets rid of everything after the number. It needs to take something like this "numberIwant (numberIdon'twant)" and turn it into this "numberIwant" and then convert that to the single data type.

I've been trying and trying to figure it out myself and it's driving me stark raving mad at times. I would appreciate some help.

Hi,

If you want to do this over any range you like (A1:A10 is given as example) then try this

Sub test()
Dim s, r

s = Chr(32) & Chr(40) & Chr(42) & Chr(41)

r = Range("A1:A10").Replace(s, vbNullString)

End Sub

 

[zeekiehafa]

That appears to work. Thank you Dosnox! Would you mind explaining what the code itself is doing? I'm unfamiliar with InStr and the Left command. Also I am unsure of why there's a minus 2.

Don't get me wrong it works and that's awesome, but I would like to understand it.

 

[Dosnox]

No worries

The Left function allows you to extract a substring from a string, starting from the left-most character.

The syntax of the the left function is:

Left(text, number_of_characters)

e.g

Left("Test",2) = Te
Left("Sample,4) = Samp


The instr function returns the position of the first occurrence of a string in another string.

The syntax of the instr function is:

InStr([start], string_being_searched, string2, [compare] )

start is optional -basically means at what character position in the search string you would like to start the search. If omitted default value is 1

string_being_searched is the string that will be searched.

string2 is the string to search for.

Compare is optional as well. The types of comparison can be found here
http://www.techonthenet.com/excel/formulas/instr.php

Where I learnt to use the instr function when I was starting out in VBA

e.g

Instr(1,"Test","e")= 2
Instr(,"Sample","mp"= 2
Instr(1,"Sample","z) = 0

So if we take the code I have written for you and break it down in steps to help your understanding.

Cell A1 at the moment = 100,000 (113)

Let's start with instr function. It searches the value in Cell A1 for the string "(" and returns 9 as "(" is the 9th character in our string.

Range("B1").Value = Left(Range("A1"), [COLOR="Red"]InStr(1, Range("A1").Value, "(")[/COLOR] - 2)

100,000 (113)

I now know where the opening bracket is in my string. Since you only require the first number in our string. I subtract 1 character for the opening bracket and another for the space leaving me with 7.

Range("B1").Value = Left(Range("A1"), InStr(1, Range("A1").Value, "(") [COLOR="red"]- 2[/COLOR])

I know that my number is 7 characters long now and utilize my left function to extract only the number and store it in the adjacent cell.

If you have any more questions or queries feel free to ask.

Glad I could help.

 

[zeekiehafa]

Wow, thank you very much for the thorough explanation Dosnox. I'll definitely be using those functions in the future.