Extracting a specific number of characters from a multiline cell

[bhenson]

Hello all,

I am attempting to reformat a list of patient contact information into something compatible with our outbound texting system. The phone number column contains multi-line cells with home, mobile, and work numbers.

I have attempted different combinations of TextBefore, TextAfter, FIND, LEN, etc., to extract the mobile number into a new column, but with little success (namely, because each cell has a slightly different type of formatting).

Column C below illustrates the multi-line cells I'm dealing with. Column D is the desired result (the first 13 characters to the left of "(Mobile)". I'll be reformatting 1,000+ rows per month, so any advice on simplifying this step would be greatly appreciated.

MobileNumberExtraction.xlsx
	A	B	C	D
1	First Name	Last Name	Phone	Mobile Number
2	John	Doe	555-444-1234 (Home Phone) 555-333-1234 (Mobile)	555-333-1234
3	Jane	Doe	555-444-5678 (Home Phone) 555-333-5678 (Mobile) 555-222-5678 (Work Phone)	555-333-5678
4	Bob	Doe	555-111-9876 (Mobile)	555-111-9876
Sheet1

 

[DRSteele]

Welcome to the forum.

I think this might help.

=IFERROR(MID(C2,SEARCH("mobile",C2)-14,12),"no mobile")

 

[hagia_sofia]

Hello, another option to test on a larger sample:

=MAP(C2:C4,LAMBDA(x,
LET(
a,TEXTSPLIT(x,CHAR(10)),
b,ISNUMBER(SEARCH(" (Mobile)",a)),
SUBSTITUTE(XLOOKUP(TRUE,b,a),"(Mobile)",""))))

 

[Scott Huish]

=TEXTJOIN("",TRUE,TEXTBEFORE(TEXTSPLIT(C2,CHAR(10)),"(Mobile)",,,,""))

 

[Rick Rothstein]

This single formula will spill all the mobile numbers in the specified range...

=TEXTAFTER(CHAR(10)&TEXTBEFORE(C2:C4,"(Mobile)"),CHAR(10),-1)

 

[bhenson]

Any one of these solutions provided me with favorable results. Thank you all for lending your expertise!

 

[bhenson]

Any one of these solutions provided me with favorable results. Thank you all for lending your expertise!

-Any one +Every one

 

[etaf]

try
=MID(C2,SEARCH("(mobile)",C2,1)-13,12)

example-ETAF.xlsx
	A	B	C	D	E	F
1	First Name	Last Name	Phone	Mobile Number
2	John	Doe	555-444-1234 (Home Phone) 555-333-1234 (Mobile)	555-333-1234		555-333-1234
3	Jane	Doe	555-444-5678 (Home Phone) 555-333-5678 (Mobile) 555-222-5678 (Work Phone)	555-333-5678		555-333-5678
4	Bob	Doe	555-111-9876 (Mobile)	555-111-9876		555-111-9876
Sheet2

Cell Formulas
Range	Formula
F2:F4	F2	=MID(C2,SEARCH("(mobile)",C2,1)-13,12)