• 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.

Spliting a text from a cell in multiple cells?

tazz

tazz

Member
It is possible to split a text from a cell in multiple cells?
For example I would like to split 4-1405 from A1 as 4 in B1, first 2 digits in C1 and last 2 digits in D1.
Thank you.
 
@tazz

as a blind sot please try this in B1,C1 & D1

B1 = LEFT(A1,1)

C1 = MID(A1,3,2)

D1 = RIGHT(A1,2)

Hope it is solve your problem other wise please inform

Thanks

Patnaik
 
Hi ,

This can be done easily using the LEFT , MID and RIGHT functions , provided your text data is in a standard format i.e. it is always 6 characters in length , the hyphen is always in the second position , and you want to split the text in the same sized chunks from the same positions within the text.

Is this so ?

If not , please post some more data ( the more the merrier ) , and use as varied data as possible , and give the desired outputs in the three columns B , C and D.

Narayan
 
Thank you for your answers. It is doing what I asked for but I run in other thing. I was hoping that if I format cells C1 and D1 as numbers I will be able to turn 07 in 7 and 03 in 3(using 4-0703 as test number). Is there a way to make this numbers ?
 
Hi ,

The result of the LEFT , MID and RIGHT functions is text ; to convert this to numbers , just use the following technique :

=LEFT(A1,1)+0

will give a number. Merely formatting the cell containing a text character / string as NUMBER will not change the data from text to numeric values.

Narayan
 
Hi,

Not sure what version of Excel you are running but this may be of use: You can use the "Text to Columns" icon in the Data menu. Select all the data and then click the button, you can then specify where you want the column lines placed and it will automatically place them in the columns you wish.

MAS
 
Text to columns does it all without changing the data format, select fixed width and in the next dialogue click once in the data to place the field lines, including one either side of the dash then in the next dialogue click in the dash Column to select it and then choose the radio button “Do not import column (skip), and that’s it click finish
 
Thank you all for your replies.
I got really good tips from you.
I think we can close this on as [Solved]
 
You must log in or register to reply here.
Back
Top