Not sure if formula or VBA will do this Extracting from Text: F123456, A123456

[chipgiii]

I would like to extract from text. I am trying to find a way to extract from text a string that starts with the letters A, C, or F and is followed by six numbers:

F123456
C654321
A345621

the cells often look like, the below three examples with the text in red what I would like to extract. Not sure if this is doable, but help is welcomed.

CR F319181 FY19-MAY18-51990-L

C 8500078841 FCR F316684 UPH 8500078841FCR A316684 UPHS

 

[jtakw]

Hi,

Based on your given samples, here's one way:

Book1
	A	B
1	CR F319181 FY19-MAY18-51990-L	F319181
2	C 8500078841 FCR F316684 UPH	F316684
3	8500078841FCR B316684 UPHS
4	8500078841FCR A316684 UPHS	A316684
5	C123456 7890123 ABCDEF	C123456
6	789012 ABCDEF C123456	C123456
Sheet483

Cell Formulas
Range	Formula
B1	=IFERROR(TRIM(MID(A1,LOOKUP(LEN(A1),SEARCH({" A?????? "," C?????? "," F?????? "}," "&A1&" ")),8)),"")

Formula copied down.

 

[chipgiii]

Hi,

Based on your given samples, here's one way:

	A	B
1	CR F319181 FY19-MAY18-51990-L	F319181
2	C 8500078841 FCR F316684 UPH	F316684
3	8500078841FCR B316684 UPHS
4	8500078841FCR A316684 UPHS	A316684
5	C123456 7890123 ABCDEF	C123456
6	789012 ABCDEF C123456	C123456

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet483​

Worksheet Formulas Cell Formula B1 =IFERROR(TRIM(MID(A1,LOOKUP(LEN(A1),SEARCH({" A?????? "," C?????? "," F?????? "}," "&A1&" ")),8)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Formula copied down.

This is working very well, very, very well!

Thanks!!!!

 

[jtakw]

You're welcome.