Removing extra characters in a cell

[Sherry]

I am working on a forecasting spreadsheet and have item numbers in column "a"
and # sold in column "b". I have run subtotal so it has added the word
"total" to the end of the part number. I then copied just the totals to
another worksheet and I am trying to use the lookup function but it won't
look up these "total part numbers" because it no longer matches my part
numbers. My problem is that my part numbers are all over the map as far as
length. Some are as short as 6 digits and some can be 13 or 14 digits so I
can't use the left function. Any suggestions?

 

[Jim Cone]

From the Edit menu, do a Replace.
Replace total with nothing (leave the "replace with" box blank)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Sherry"
wrote in message
I am working on a forecasting spreadsheet and have item numbers in column "a"
and # sold in column "b". I have run subtotal so it has added the word
"total" to the end of the part number. I then copied just the totals to
another worksheet and I am trying to use the lookup function but it won't
look up these "total part numbers" because it no longer matches my part
numbers. My problem is that my part numbers are all over the map as far as
length. Some are as short as 6 digits and some can be 13 or 14 digits so I
can't use the left function. Any suggestions?

 

[Bernard Liengme]

Tell us what you mean by: I have run subtotal so it has added the word
"total" to the end of the part number

 

[HKaplan]

If I undertand what you are trying to accomplish, use this formula to
strip the word TOTAL from the subtotal cell text:
=LEFT(A6,LEN(A6)-6). Where for example A6="123part Total". This
formula would return "123part".

Use that in your lookup formula, like
=vlookup(LEFT(A6,LEN(A6)-6),mydatatable,2,false).