part of text in cell copy to another cell

[PH6]

Hi

This is the question:

in a cell: word1 word2 word3 word4 word5 etc

I want to take out of this cell the text start with SO***** (2 letters+5 digits).
This text is placed random in the cell.

Pls advs, is this possible with excel 2010 or only with VBA?

Thnks in advance,

Peter

 

[wigi]

That is possible.

=MID(A1,FIND("SO",A1),7)

I used cell A1 as an example.

It will be more difficult however if SO can occur multiple times in the same cell, followed yes or no by 5 digits. Is this the case?

 

[jim may]

Can you use this?

Excel Workbook
	A	B	C
1	Peter	Peter
2	Paul	Paul
3	MarySO12345	Mary	SO12345
4	Bob	Bob
5	TeSO99999d	Ted	SO99999
6	Carol	Carol
7	Alice	Alice
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1,C1,"")
C1	=IF(ISERROR(MID(A1,FIND("SO",A1),7)),"",MID(A1,FIND("SO",A1),7))

 

[PH6]

ahhhhummm,

Great, this works!! (no multiple times, lucky!)

Thanks for this quick response.

Peter