Mid or Right

  • Thread starter Thread starter bg
  • Start date Start date
B

bg

Good Morning,
Is there a way to take data from the point in the cell that it contains a -
(hyphen)
for example, the length can chg but it will always be 6 numbers and then a
hypen and then the part I want (ie . Sales order 1 etc..)

123456 - Sales order 1
234567 - Sales order 15
 
=right(A2,len(A2)-find("- ",A2))

Added a space at the end of the find to ensure you start with the alpha.
 
Try

=MID(A1,FIND("-",A1)+2,LEN(A1))

where A1 has the original data. This assumes that there is indeed a
"-" character in the string.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Another way to do it is to copy the range (a single column) to where you want
it.

Select that new column (or the old column if you can lose the original text)
Edit|Replace
what: (spacebar)* (space character followed by an asterisk)
with: (leave blank)
replace all

This will leave the leading digits--but it'll become a real number. You may
want to format it to keep any leading 0's.

Format|cells|number tab|Custom
000000
 
You say the hyphen comes immediately after your 6 digit number, but your
examples show a space between them and also one after the hyphen. So, if
there really are *no* spaces in either location, then you can use this...

=MID(A1,8,99)

where the 99 assumes your text after the hyphen is no longer than 99
characters in length (if it can be, just make the 99 value larger). If there
are spaces in both locations, you can do this instead...

=MID(A1,10,99)

If there is no space before the dash, but there might or might not be a
space after it, then you can do this...

=TRIM(MID(A1,9,99))
 
And if you are using 2007 you may have very long text entries in the cell, in
which case

=MID(C6,9,9^9)
 
Back
Top