text to columns

  • Thread starter Thread starter bwduncan
  • Start date Start date
B

bwduncan

I've exported a worksheet that put are customer id # and name in the same
column.
Of course our customer id aren't all the same length and some of the names
also have a "-" in them. Is there a way to seperate this by the first hyphen
only and to disregard the second one?
 
This formula will display the text to the left of the first dash...

=LEFT(A1,FIND("-",A1&"-")-1)

And this formula will return all the text after that first dash...

=MID(A1,FIND("-",A1&"-")+1,99)

The second formula assumes your entries are not longer than 99 characters.
If they could be longer, just change the 99 to 999 instead. Oh, and these
formulas can be copied down as needed.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

fixed decimal formatting 2
Partial text returned from a query 0
How to combine three columns information 5
dependants for a drop down list 6
text to columns 1
Using Sumproduct - Excel 2003 3
Text to Column will not cooperate 2
Excel Comparing two columns 1

Back
Top