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

[jimmynora]

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

 

[Weaver]

In the case of 0:, would you need to keep the : (unclear from your example)

 

[jimmynora]

disregard the ":" when pasting the value in the cell

 

[DonkeyOte]

since x-posted: http://www.excelforum.com/excel-pro...g-text-from-a-string-in-a-cell-excel-vba.html

 

[Darren Bartrup]

oooh, oooh, I know this one.....

is it...

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

?

Course, might've copied that from another forum.

 

[jimmynora]

code does not work

 

[DonkeyOte]

That's very helpful. Unfortunately I appear to have misplaced my crystal ball - in the meantime perhaps you could provide some more information ?

Excel Workbook
	A	B
1	shawn012k 0: jamespdf: 3211:pdf: 2: pdf:	jamespdf: 321
2	shawn012k 0: james ajskajskjaksj kajsaksjaksjakjsa pdf: 321asklakslalskl1:pdf:	james ajskajskjaksj kajsaksjaksjakjsa pdf: 321asklakslalskl
3	askajskajsak
Sheet4

In the above UK delimiters (and function name) are used.

 

[jimmynora]

Thank You Works now

 

[jimmynora]

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]

FWIW, the cross post has been updated with same question:
http://www.excelforum.com/excel-pro...a-string-in-a-cell-excel-vba.html#post2404464