separating numerical and alpha in a cell

[nojpatel]

Dealing with a large data of employees timing recording by the time tracking software . The source data that needs to be split is TR0900-1400 ( TR denotes the location and the employee worked from 900 hours to 1400 hours )

As the data runs into thousands of rows each week and would not like to go to Data and TEXT TO COLUMN to separate the Alpha and numbers.

Can someone please give me a formula that i can use in cell B1, C1 and D1 to get the below result from the SOURCE DATA IN CELL A1 which is TR0900-1400 to TR in cell B1, then 0900 in cell C1 and finally 1400 in cell D1 .

Thank you

Manoj

 

[Dhamo]

Hi Manoj,


You can use 'Text to column' from Data option.


Thanks

 

[Faseeh]

Hi nojpate,


Assuming your data in Col A,


1. In B enter:

=LEFT(A1,MIN(IF(ISNUMBER((VALUE(MID(A1,ROW($A$1:$A$25),1)))),ROW($A$1:$A$25)))-1)


Press Ctrl+Shift+Enter


2. In C enter 


[code]=MID(A1,LEN(B1)+1,FIND("-",A1)-LEN(B1)-1)

3. In D enter


=TRIM(MID(A1,FIND("-",A1)+1,99999))[/code]


Hopefully that helps,


Faseeh

 

[nojpatel]

Awesome Boss you are a magician !!! I was stuck with using LEFT and RIGHT formula with little result....your answer solves my problem. Thank you very much.Kind regards. Manoj

 

[oldchippy]

Hi,


Just adding to Faseeh formula's in case you want to use the time values as actual times the formulas could be adjusted for C1 and D1 by using these


=TEXT(MID(A1,LEN(B1)+1,FIND("-",A1)-LEN(B1)-1),"00:00")*1


=TEXT(TRIM(MID(A1,FIND("-",A1)+1,99999)),"00:00")*1


Format those cells to TIME > Custom > hh:mm

 

[shrivallabha]

Faseeh's first formula can be replaced by this normal formula:

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