seperating text

edsjr

New Member
Joined
Mar 27, 2002
Messages
33
I have to seperate some of the items in each cell.......for instance, now it is in as abc123456 I need to get the "abc" into a seperate cell and leave the 123456. I have 30,000 rows to do so I dont really want to do it one at a time.

Thanks
Ed.
This message was edited by edsjr on 2002-03-28 18:28
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi Ed


Have you tried the Text to Columns.. Wizard found under Data?

If this won't work for you then just tell us what the criteria is that should be met, eg first 3 characters, only letters etc.
 
Upvote 0
I have to seperate some of the items in each cell.......for instance, now it is in as abc123456 I need to get the "abc" into a seperate cell and leave the 123456. I have 30,000 rows to do so I dont really want to do it one at a time.

Ed,

If the alpha part of the entries is of fixed length (it's 3 for "abc123456", the option Data|Text to Columns > Fixed Width will do what you want.

If the alpha part is of variable length and assuming that the first target entry is in A1,

In B1 enter:

=SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{".",0,1,2,3,4,5,6,7,8,9},""))))),"")

This will give you the alpha part.

In C1 enter:

=SUBSTITUTE(A1,B1,"")+0

This will give you the numeric part.

Activate B1 or C1 or both, give a double click on the little black square in the lower right corner of the cell in order to copy down the formula.

Suppose that you wanted to have the results in B, select all of the cells in B, copy it, and do a Edit|Paste Special >Values to get rid of the formulas.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top