text to columns

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?
 
R

Rick Rothstein

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.
 

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

Partial text returned from a query 0
How do I make a vertical report export into a vertical report? 3
How to combine three columns information 5
How to make columns based on repeated rows?? 4
formating of text file 1
matching & combining data from seperate worksheets 2
text to columns 1
Sorting rows of 2 Seperate Columns Together 2

Top