parse cell data

[I2omani]

Hi,
I have over 3000 rows of data similar to "MAYALB93.0106.04.39" in column E1. I want to parse it so that ...
A1 = MAYALB93
B1 = 0106
C1 = 04
D1 = 39

so far i was able to come up with this formula below , but i really feel lost

in A1 i did this:

=LEFT(E1,FIND(".",E1)-1)

in B1 i did this:

=MID(E1,FIND(".",E1)+1,FIND(".",E1,FIND(".",E1)+1)-1-FIND(".",E1))

I would really appreciate any suggestions

 

[mrshl9898]

Data > Text to Columns, select delimited > next, Tick other and enter a full stop next to it, press finish

 

[I2omani]

Data > Text to Columns, select delimited > next, Tick other and enter a full stop next to it, press finish

Thank you . but I am keen to use the formulas.

 

[Peter_SSs]

.. I am keen to use the formulas.

Is there a particular reason for that? This sort of issue is exactly what Text to Columns is designed for.
With a couple of extra tweaks on what mrshl9898's suggested, you could have the results in columns A:D as required and preserve any leading 0's like in your "0106" and "04"

 

[I2omani]

thank you. but when i try the text to column , it eliminates leading 0's

keep in mind that some rows might not have leading 0 and just start 144.XX

 

[Peter_SSs]

If you really do want formulas, try this, copied across and down.

Excel Workbook
	A	B	C	D	E
1	MAYALB93	0106	04	39	MAYALB93.0106.04.39
TTC

 

[Peter_SSs]

thank you. but when i try the text to column , it eliminates leading 0's

That's why I said

With a couple of extra tweaks .. you could .. preserve any leading 0's like in your "0106" and "04"

In the Text to Columns, in Step 3

- You can choose the Destination so you could type A1 into that box
- In the data preview window at the bottom, click in the second column where you see the first number (eg 0106) & the column should go black. Then at the top section of the dialog, select the 'Text' button. repeat this step for any other columns that may contain leading 0's
- Finish