Extract text from cell data

[jnyfiveoh]

Good morning,

I was wondering if there is a function or formula that can pull text from a cell string in a specific format

EXAMPLE:

CP-6921
CP-8811
CP-6921-C-K9
CP-8811-K9 V04
PID VID CP8811.K9 V04
PID VID: CP-6921-K9 = V02

I would like to pull the data from the above into the following format

CP-0000

Any insight would be appreciated

Thanks!

[AliGW]

Does what you want to extract always start with CP? If not, provide a more representative sample of data in a workbook.

[gondal]

use below in B1

=SUBSTITUTE(MID(A1,FIND("CP",A1),7),".","")

or want like CP-0000, if dash exist or not than

="CP-"&MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),4)

[Bo_Ry]

Assume that data is in Column A
Please try B1
="CP-"&MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1/17)),4)

[jtakw]

Hi,

If what you want extracted Always starts with "CP" and you want the extracted results to be in "CP-0000" format as you described in OP...

This would work regardless of Where within the Text string is the value you want extracted, or if you have Other numbers preceding the value you want extracted:

Excel 2016 (Windows) 64 bit

	A	B
1	CP-6921	CP-6921
2	CP-8811	CP-8811
3	CP-6921-C-K9	CP-6921
4	CP-8811-K9 V04	CP-8811
5	PID VID CD8811.K9 V04
6	PID VID CP8811.K9 V04	CP-8811
7	PID VID: CP-6921-K9 = V02	CP-6921
8	C-K9-CP-6921 V04	CP-6921
9	K9 = V02 PID VID: CP6921	CP-6921

Sheet: Sheet99

Excel 2016 (Windows) 64 bit

	B
1	=IFERROR(REPLACE(MID(SUBSTITUTE(A1,"-",""),SEARCH("CP",SUBSTITUTE(A1,"-","")),6),3,,"-"),"")

Sheet: Sheet99

Formula copied down.