Text to Columns

[DaveB]

Trying to separate the "-04" from the rest of Cell A1, but
keep getting either a "4" or a "-4". Thanks in advance for
your assistance. DaveB


HAVE: (Cell A1) 5R01MH060244-04

WANT: (Cell A1) 5R01MH060244
(Cell B1) -04

 

[Frank Kabel]

Hi
use the following formula in B1:
=MID(A1,FIND("-",A1),10)

 

[Andy Brown]

Trying to separate the "-04" from the rest of Cell A1, but

keep getting either a "4" or a "-4". Thanks in advance for
your assistance. DaveB


HAVE: (Cell A1) 5R01MH060244-04

WANT: (Cell A1) 5R01MH060244
(Cell B1) -0

If they're all that format, you could try Data -- Text to Columns. Under
Step 1, data type = fixed width, click Next. Under Step 2, create a break
line just before the "-". Click Finish.

HTH,
Andy

 

[dave gremaud]

Dave,

If you go to Data/Text to Columns you can use either
delimited or fixed width. If the "-04" is needed and is
your cells are always the same width then fixed width
should work best, if not try the delimited. With
delimited check the "other" box and put a "-" in the box
next to it. This will automatically separate your cells
where ever it sees that sign. I use this frequently and
it works well.

Hope that helps. I would be happy to assist further if
needed. My email is (e-mail address removed).

Dave

 

[Andy Brown]

If you go to Data/Text to Columns you can use either

delimited or fixed width. If the "-04" is needed and is
your cells are always the same width then fixed width
should work best, if not try the delimited. With
delimited check the "other" box and put a "-" in the box
next to it. This will automatically separate your cells
where ever it sees that sign. I use this frequently and
it works well.

Using delimited strips out the delimiter. Even if DaveB specifies column
data format as Text under Step 3, he'll get "04". The question stated
"trying to separate the "-04"" ; whether retention of "-" is required is not
entirely clear. Either way, he should be able to get somewhere with all of
the above. Thanks for jumping in.

Rgds,
Andy