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.

[TABLE="width: 454"]
[TR]
[TD="class: xl65, width: 454"]CR F319181 FY19-MAY18-51990-L[/TD]
[/TR]
[/TABLE]
[TABLE="width: 454"]
[TR]
[TD="class: xl65, width: 454"]C 8500078841 FCR F316684 UPH

[TABLE="width: 454"]
[TR]
[TD="class: xl65, width: 454"]8500078841FCR A316684 UPHS
[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[/TABLE]

 

[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
CR F319181 FY19-MAY18-51990-L	F319181
C 8500078841 FCR F316684 UPH	F316684
8500078841FCR B316684 UPHS
8500078841FCR A316684 UPHS	A316684
C123456 7890123 ABCDEF	C123456
789012 ABCDEF C123456	C123456

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

</tbody>

Sheet483​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IFERROR(TRIM(MID(A1,LOOKUP(LEN(A1),SEARCH({" A?????? "," C?????? "," F?????? "}," "&A1&" ")),8)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down.

This is working very well, very, very well!

Thanks!!!!

 

[jtakw]

You're welcome.