Extracting Text from a string in a Cell (Excel VBA)

**jimmy_nora** · 10-20-2010, 06:41 AM

--------------------------------------------------------------------------------

I have a range of cells that contains data in the form on strings. I need to extract a part of this string and paste it (to the empty cell on the right).

Range "AV" contains the cells with strings
I need to extract the data between "0" OR "0:" and till the end of the string in the cell OR till "1"

Can anyone show me how to do this with a Macro (VBA code) or anyway else ? Thank You

example :

(String in a cell)

shawn012k
0: james
pdf: 321
1:
pdf:
2:
pdf:

Extracted Data (pasted in the cell to the right):
james
pdf: 321

**DonkeyOte** · 10-20-2010, 07:04 AM

jimmy_nora, welcome to the board.

I am sure you will have the Forum Rules and as such will be aware that if you choose to post the same question in more than one online Forum we ask you disclose all relevant links.

http://www.mrexcel.com/forum/showthread.php?t=502979

**DonkeyOte** · 10-20-2010, 07:08 AM

Incidentally why do you need VBA to do this - would a formula not suffice ?

B1:
=TRIM(REPLACE(LEFT(A1;FIND(CHAR(10)&"1:";A1&CHAR(10)&"1:")-1);1;FIND("0:";A1&"0:")+1;""))
wrap text activated

**jimmy_nora** · 10-20-2010, 08:12 AM

what do you mean by "disclose all relevant links" ?

**jimmy_nora** · 10-20-2010, 08:12 AM

code does not work !

**StephenR** · 10-20-2010, 08:35 AM

You should tell us that you have posted in other forums and provide the link so people do not waste their time answering questions which have already been solved elsewhere.

**jimmy_nora** · 10-22-2010, 03:39 AM

Is there anyway I can further manipulate this formula to trim only the part between "1" or "1:" and "2:" or "end of cell" ?

In this case I need to ignore the first 1: because it occurs before the 0:

example:

user bio1:
0: asdasdas
1: asdasds
2:
3: asdas

required data:
asdasds

**DonkeyOte** · 10-22-2010, 03:48 AM

jimmy_nora please at least detail the fact that you have updated your thread at MrExcel with the same - granted it remains the same cross post (detailed by me) but it's not clear from your post you have done this.

**ashwanik_aggarwal** · 10-22-2010, 04:11 AM

hi i think this will be helpful.
=LEFT(RIGHT(B4,LEN(B4)-FIND("1",B4)),FIND("0",RIGHT(B4,LEN(B4)-FIND("1",B4)))-1)

this formula gives you the string between 0 and 1.
you can change the character in place of 0 or 1.

**jimmy_nora** · 10-22-2010, 04:48 AM

Originally Posted by ashwanik_aggarwal

hi i think this will be helpful.
=LEFT(RIGHT(B4,LEN(B4)-FIND("1",B4)),FIND("0",RIGHT(B4,LEN(B4)-FIND("1",B4)))-1)

this formula gives you the string between 0 and 1.
you can change the character in place of 0 or 1.

your code doesn't work for me

**DonkeyOte** · 10-22-2010, 05:10 AM

Originally Posted by jimmy_nora

In this case I need to ignore the first 1: because it occurs before the 0:

example:

user bio1:
0: asdasdas
1: asdasds
2:
3: asdas

required data:
asdasds

The formula provided previously already returns "asdasds".

If you can not replicate I would suggest you post a sample file to better illustrate the problems you are experiencing.

edit: ignore that - I see what you mean now

**ashwanik_aggarwal** · 10-22-2010, 05:20 AM

=LEFT(RIGHT(B4,LEN(B4)-FIND("1",B4)),FIND("0",RIGHT(B4,LEN(B4)-FIND("1",B4)))-1)

Paste this formula in cell C4.
and Enter the string into B4.
It will work definetly

**DonkeyOte** · 10-22-2010, 05:30 AM

@jimmy_nora

I have posted an update to your thread at MrExcel.com with screenshot of output

http://www.mrexcel.com/forum/showthread.php?t=502979

I have added a file here.