How to split text & numbers in one cell to multiple cells

[MaheshK5277]

Hi all,

I need to split text in one cell to multiple cells. the text contains numbers as well & in fact the starting character within the cell is a number.

see attached file & kindly provide solution

Thanks

[bebo021999]

Could you supply more rows of sample?

[MaheshK5277]

Hi Bebo,

rows added. see file attached

[udprocks]

or just simly use "Text to column" for ur given example

Text to column-----------fixed width

[AliGW]

or just simly use "Text to column" for ur given example

Text to column-----------fixed width

It doesn't work because the columns are not of fixed width!

[udprocks]

It doesn't work because the columns are not of fixed width!

yes, ali but that was for that first given example file.

[udprocks]

It doesn't work because the columns are not of fixed width!

yes, ali but that was for that first given example file sorry.

[AliGW]

yes, ali but that was for that first given example file sorry.

Yes, and it didn't work with that file, either!!! What would need to be in the second column were of different lengths.

[shukla.ankur281190]

Ok Let try

C6=LEFT(A6,FIND(" ",A6)-1)
D6=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,E6,""),F6,""),G6,""),H6,""),C6,""))
E6=IFERROR(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,F6," "),G6," "),H6," "))," ",REPT(" ",50)),50)),0)
F6=IFERROR(-LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A6,"-",REPT(" ",50)),50))," ",REPT(" ",50)),50),0)
G6=IFERROR(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A6,"-",REPT(" ",50)),ABS(F6),REPT(" ",50)),50))," ",REPT(" ",50)),50)),0)
H6=IFERROR(TRIM(RIGHT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A6,"-",REPT(" ",50)),ABS(F6),REPT(" ",50)),50))," ",REPT(" ",50)),50)),0)

and drag down

[MaheshK5277]

Hi Ankur,

Yes it works...many thanks bro...!!!

[LeoTaxi]

Maybe with a code ?

Sub test()
Dim lasstrow As Long, mystring As String, r As Integer, x As Integer, j As Integer, i As Integer
Dim col1 As String, col2 As String, col3 As String, col4 As String, col5 As String, col6 As String
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For i = 6 To lastrow
    mystring = Replace(Range("A" & i), ".", "")
    r = Len(mystring) - Len(Replace(mystring, " ", ""))
    ReDim mypos(r)
    j = 1
    For x = 1 To r
        mypos(x) = Application.Search(" ", mystring, j)
        j = mypos(x) + 1
    Next
    col1 = Left(mystring, mypos(1))
    col2 = Mid(mystring, mypos(1) + 1, mypos(r - 3) - (mypos(1) + 1))
    col3 = Mid(mystring, mypos(r - 3) + 1, mypos(r - 2) - mypos(r - 3) - 1)
    col4 = Mid(mystring, mypos(r - 2) + 1, mypos(r - 1) - mypos(r - 2) - 1)
    col5 = Mid(mystring, mypos(r - 1) + 1, mypos(r) - mypos(r - 1) - 1)
    col6 = Right(mystring, Len(mystring) - mypos(r))
    Range("C" & i).Resize(1, 6).Value = Array(col1, col2, col3, col4, col5, col6)
    j = 1
Next
End Sub

Kind regards
Leo

[MaheshK5277]

Hi leo,

What a wonderful piece of work...works amazingly well...! Many thanks to you..