Removing extra characters in a cell


S

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

Advertisements

J

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

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
 
Ad

Advertisements

H

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).
 

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

Top