Returning 10 digits from text string?

[mucusboy]

I have a formula which has worked perfectly for some time, but I now have some variables in my source data and am struggling to amend it.

Essentially I need to search a string of text and return a 10 digit number. Previously this always started with a “41” so I could use the formula:

=IFERROR(LEFT(TRIM(RIGHT($W4,(LEN($W4)-SEARCH("41",$W4)+1))),10),"")

There’s probably a cleaner way of doing this I know…

However, the 10 digit number may now start with either a “41”, “42”, “91” or “92”. I am really not sure how I can amend to find the solution. Can anyone help?

 

[Phuoc]

Try:

=MID($W4,MIN(IFERROR(SEARCH(4,$W4),1000),IFERROR(SEARCH(9,$W4),1000)),10)

 

[sergioMabres]

Or try this

[COLOR=#444444][FONT=Calibri]=MID($W4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]$W4[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]&{0,1,2,3,4,5,6,7,8,9})),10)[/FONT][/COLOR]

Cheers
Sergio

 

[mail2master]

You can Try this one. Worked for me last time I checked:

=MID(W4,MIN(IF((ISNUMBER(MID(W4,ROW(INDIRECT("1:"&LEN(W4))),1)+0)*ROW(INDIRECT("1:"&LEN(W4)))),ISNUMBER(MID(W4,ROW(INDIRECT("1:"&LEN(W4))),1)+0)*ROW(INDIRECT("1:"&LEN(W4))))),10)
Confirm with Ctrl+Shift+Enter

 

[mucusboy]

Try:

=MID($W4,MIN(IFERROR(SEARCH(4,$W4),1000),IFERROR(SEARCH(9,$W4),1000)),10)

Hi Phuoc,
Thanks so much for this, it is nearly perfect.

I didn't account for the fact there are 2 numbers in the string, so it is not always calling the correct one. I figured out if I was searching for “4100”, “4200”, “9100” or “9200” it would always work, but I couldn't manage to update the formula to make this work. This was my attempt:

=MID($X2,MIN(IFERROR(SEARCH(4100,$X2),1000),IFERROR(SEARCH(4200,$X2),1000),iferror(search(9100,$x2,111),iferror(search(9200,$x2,111),10))))

Am I far off or is this even possible?

 

[mucusboy]

You can Try this one. Worked for me last time I checked:

=MID(W4,MIN(IF((ISNUMBER(MID(W4,ROW(INDIRECT("1:"&LEN(W4))),1)+0)*ROW(INDIRECT("1:"&LEN(W4)))),ISNUMBER(MID(W4,ROW(INDIRECT("1:"&LEN(W4))),1)+0)*ROW(INDIRECT("1:"&LEN(W4))))),10)
Confirm with Ctrl+Shift+Enter

Thanks for your reply! I couldn't get this to work and kept returning #VALUE !

 

[mucusboy]

Or try this

[COLOR=#444444][FONT=Calibri]=MID($W4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]$W4[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]&{0,1,2,3,4,5,6,7,8,9})),10)[/FONT][/COLOR]

Cheers
Sergio

Really appreciate your response. This is pulling our 10 digits but unfortunately I didn't realise that I have multiple numbers throughout the text string so it's not always finding the one I was after. I need to just find them if they start with “4100”, “4200”, “9100” or “9200”

 

[steve the fish]

How about the array formula:

=MID(TRIM(W4),MIN(IFERROR(FIND({41,42,91,92},W4),"")),10)

This will not work if you dont enter CTRL-SHIFT-ENTER

 

[sergioMabres]

I see in that case you can use =MID($W4,MIN(FIND({41,42,91,92},$W4&{41,42,91,92})),10)
Cheers
Sergio

 

[mucusboy]

I see in that case you can use =MID($W4,MIN(FIND({41,42,91,92},$W4&{41,42,91,92})),10)
Cheers
Sergio

Thanks Sergio, this has worked perfectly Really appreciate your help