Split String From A Cell Based On Multiple Criteria

**ssanjju** · 01-05-2013, 12:44 AM

Hi Excel Masters,

Please provide you support in solving my below challenge with creating a formula fused with three criteria.

Say if the cells:
A1 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS A 02/19/98 701-483-3456
A2 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS B 02/19/98 701-483-3456
A3 = 3AB LT00022788 TH0000243428 JACKSON,LUCAS TC 02/19/98 701-483-3456

And When I put the below formulae in B1, B2 & B3:
=LEFT(A1,FIND(" A ",A1,1)-1)
=LEFT(A2,FIND(" B ",A1,1)-1)
=LEFT(A3,FIND(" TC ",A1,1)-1)

The resultant would be:
B1= 3AB LT00022788 TH0000243428 JACKSON,LUCAS
B2= 3AB LT00022788 TH0000243428 JACKSON,LUCAS
B3= 3AB LT00022788 TH0000243428 JACKSON,LUCAS

Challenge is to put a common formula that I can drag down from B1 to B3 that should give the above results.

Thanks in advance!!

**FDibbins** · 01-05-2013, 12:55 AM

if the format is consistent throughout, or at least regarding the date positioning, you could use something like this....

=LEFT(A1,SEARCH("/",A1,1)-6)

an alternative might also be to use text-to-columns, based on "fixed width", remove all-but-1 of the dividers, and then position that where you want it

**Teethless mama** · 01-05-2013, 01:12 AM

B1: =left(a1,find(" ",a1,find(",",a1)))

**ssanjju** · 01-05-2013, 03:21 PM

Thank you for the response. But the main problem is that the data is not consistent as its manually entered by multiple users. The form contains the below fields to be entered and users type it all differently leaving few fields blank most of the times.

TYPE/ID#/ACCT#/NAME/CAT/DOB/HOMEPHONE/WORK PHONE/CO APPL/PRI LOAN

Sample:
3AB LT00022788 TH0000243428 JACK,LUC A 02/19/98 701-483-3456 701-483-3456 Andy,Pat USA
3AB LT00022788 TH0000243428 JACKSON,LUCAS B 02/19/98 701-483-3456 USA
3AB LT00022788 TH0000243428 JACKSON,LUCAS TC 02/19/98 701-483-3456 701-483-3456
3AB LT00022788 TH0000243428 JACKSON,LUCAS C 02/19/98 USA
3AB LT00022788 TH0000243428 JACKSON,LUCAS IF 02/19/98 Andy,Patricia
3AB LT00022788 TH0000243428 STEINER,CORBAN PAUL G USA

I have to manually segregate the data based on the above headers and I was trying to find a workaround wherein I can put some formulas in place so that the relevant data sits in the corresponding columns automatically. Please advise.