Splitting Value

  • Thread starter Thread starter medlint
  • Start date Start date
M

medlint

I have data in Excel I want to split into separate
columns. Currently, I copy the data to Word, covert the
table to text (separating at the hyphen) and then paste it
back into Excel. I have been experimenting with the =LEFT
and =RIGHT formulas, but nothing I've found in Excel works
as well as Word's convert to text feature.

Here's some sample values.
USA-2004-0001-WW
USA-2004-0002-ZZZ
UK-2004-0001-ZZZ
UK-2004-0002-WW

The middle two values are ALWAYS 4 digits.
The first and fourth values are 2 OR 3 digits.

Is there any function/formula in Excel that can split the
data as effectively as Word's convert to text feature?
 
Select the column, go to Data > Text to Columns.
Select "Delimited", press Next, check the "Other" box and
insert "-" (no quotes) into the box.

HTH
Jason
Atlanta, GA
 
You can use the dash as a delimiter. Highlight the range
of values you want to separate. Go to Data on the toolbar
and choose Text to Columns. Make certain that Delimited is
chosen as the Original file type and choose next. Put a
check mark in the box for Other and type in a dash. Click
Finish.

I hope that's what you were looking for.

tod
 
You can use Data - Text to Columns in Excel. Select the
cells with the data you want to separate, then select
Data - Text to Columns. A dialog box will open asking you
to specify Delimited or Fixed Width; you'll want
Delimited. On the next screen you'll need to specify a
delimiter; check "Other" and type a hyphen into the box
next to that. Click "Finish" and you should end up with
all your data separated.
 
Back
Top