Extracting Text and Numbers from a cell with varying lengths and no spaces

Hello,

I have a set of cells from Column A from which I have to extract information for a different cell.
For instance, From Column A
AUS177-4M
Canada551-3W
MEX316-3W
US160-3Mo
ARG265-2W
MEX363-5Mo
US351-4W
GER195-6Mo

1.I have to extract the Country, which varies between cells.
2. I have to extract the # following the country and before the "-"
3. As well as the Information afterward.

All of the tutorials I have watched require things like spaces to extract any idea on how to do it?

show expected result or will be better if you attach example excel file with Before and After data. Read big yellow banner at the top of this site

Hi I have done it Sorry for multiple posts. The first time it crashed and I didn't think it posted

sure,, next time attach excel file as described at the top yellow banner
and show your own solution for others here
have a nice day

Assuming the data starts in cell A1, use the following formulae in the cells stated:

C1: =LEFT(A1,FIND("-",A1)-4)

D1: =MID(A1,LEN(C1)+1,3)

E1: =RIGHT(A1,LEN(A1)-FIND("-",A1))

then copy down as required. Note that the first two assume that the numbers are 3 digits, as in all your examples.

Hope this helps.

Pete

Thanks for the info, Trevor.

Maybe a Mod will merge the duplicates into one thread (it used to happen quite often).

Pete

Actually, looking at the posting times of both threads, it is the other one which is a duplicate of this one, so the other responses should be merged into this one and then the other thread could be deleted. Although there is activity on the other thread, no solution has been suggested yet.

Pete

Maybe not the most efficient, but they should work:

B2:
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

C2:
=MID(A2,LEN(B2)+1,SEARCH("-",$A2)-LEN(B2)-1)

D2:
=TRIM(LEFT(RIGHT(A2,LEN(A2)-SEARCH("-",A2)),MIN(SEARCH({"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},RIGHT(A2,LEN(A2)-SEARCH("-",A2))&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-1))

E2:
=LET(a,RIGHT(A2,LEN(A2)-SEARCH("-",A2)),RIGHT(a,LEN(a)-MIN(IFERROR(SEARCH({"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"},a),""))+1))

EmployeeCode		Location	EmpID	PlantID	PayPeriod
AUS177-4M		AUS	177	4	M
Canada551-3W		Canada	551	3	W
MEX316-3W		MEX	316	3	W
US160-3Mo		US	160	3	Mo

Please Login or Register  to view this content.