• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Seperate Number and Text

Umesh Chahar

Umesh Chahar

Member
Hi all ,
i Have a data which includes Number and text in single cell and i want to seperate the number and text into seperate column , but the problem is that neither the text not the number has fixed length they are varying means thet have variable length so please suggest me any generallised formula so that i can seperate number and text into seperate cells.


umesh 1235
jitendra 74654654
amol 561
ankit 35651
shobhit 2
dpji 51556
khyati 35213
sharjil 65446545
sapna 563
romi 45621
udisha 566664
yedhu 1
umesh 9565
jitendra 5
amol 57
ankit 75
shobhit 75
dpji 6557
khyati 755
sharjil 54
sapna 84984
romi 5
udisha 775
yedhu 4
umesh 42
jitendra 654654654
amol 45665
ankit 21
 
Suppose your data is in A1...

Then in B1 use TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
In C1 use TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
 
Umesh - you can replace 255 with Len(A1) and the formula would look like this -

B1 - TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
C1 - TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
 
Hi Asheesh the formula which you have given me is not working , infact what it is doing it is repeting the string for 255 times , please suggest any new formula
 
Umesh - not sure why isnt it working for you..please check once again..it is working fine here basis the examples given above..

Check the attached....

Let me know if you still face any issue and share the file that you are using..
 

Attachments

  • Umesh_Split Data.xlsm
    8.8 KB · Views: 10
You must log in or register to reply here.
Back
Top