Removing/Adding text to cells

[farzyness]

Hi,

I have about 700 items that when I try to search and replace the prefix (i.e: cell contains "UPC 070235856985" and I'm trying to remove UPC and the space from the front of the cell so that I can vlookup the number on a different spreadsheet. Both fields are set up as text,) it removes the 0 in front of the number, rendering vlookup useless.

When trying to record a macro and running it, all it does is replace all cells with the original cell where I recorded the macro.

Any help would be MAGNIFICENT! Thank you!

[Whizbang]

You could use a wildcard in your vlookup. That way no matter how it starts, it will find the number you need. No replacement necessary.

=Vlookup("*YOUR NUMBER",YOURTABLE,1,False)

Note the *

[Richard Buttrey]

Hi,

Why not just use a helper column and enter

Please Login or Register  to view this content.

copy it down then Copy the helper column and paste it back as values to your original column.

HTH

[meyero90]

try this assuming your word is in A1:

=RIGHT(TRIM(A1),FIND(" ",TRIM(A1))+8)

[farzyness]

Hi,

Why not just use a helper column and enter

Please Login or Register  to view this content.

copy it down then Copy the helper column and paste it back as values to your original column.

HTH

Absolutely magnificent. Thank you so much!