Text 2 Columns with certain conditions

[karthick87]

I have a data which I would like to convert into "Text to columns" but with certain conditions like the following.

1. The text before the numeric characters has to be in 1 column and when the numeric character starts we should apply "Text to Column" with space as delimiter.

I have attached a sample excel file here to give better understanding ( it contains two sheets (Raw_Format and Expected_Output)).

 

[Marc L]

Hi !

The easy way for using Text To Column Excel inner feature :
insert a comma between each part !
As here obviously space may not be a valid delimiter …

First number always starts by 1 ?
​

 

[karthick87]

@Marc L, thank you for helping me out. Nope, first number is not always 1, it can be anything between "0 to 9".

 

[bosco_yip]

Try this formula solution.

1] In "Expected_Format" sheet A1, copied down :

=IF(Raw_Format!A1="","",LEFT(Raw_Format!A1,MIN(FIND({0,1,2,3,4}+{0;5},Raw_Format!A1&1/17))-2))

2] In "Expected_Format" sheet B1, copied across to D1 and all copied down :

=IF(Raw_Format!$A1="","",TRIM(LEFT(RIGHT(SUBSTITUTE(Raw_Format!$A1," ",REPT(" ",30)),(4-COLUMN(A1))*30),30)))

Regards
Bosco

 

[Khalid NGO]

FIND({0,1,2,3,4}+{0;5}

Hi Bosco,
Cleaver

Regards,