Removing apostrophes before a text for a vlookup to work

odinie01

New Member
Joined
Apr 6, 2013
Messages
5
hi all,

I am working on a task by using a simple vlookup but the cells seem to have an apostrophe before the works i am looking up and this is returning an N/A result for some of the cells.

what can i do to get rid of the apostrophe for the vlookup to work?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
what type of data is after the apostrophe? I'm assuming numbers

no its text like names of industries. i have searched for a solution on google but all i can get are solutions for numbers like applying *1 to the formula bar etc but this doesn't work for txts
 
Upvote 0
no its text like names of industries. i have searched for a solution on google but all i can get are solutions for numbers like applying *1 to the formula bar etc but this doesn't work for txts

would it be better if i attached the data?
 
Upvote 0
well, you have a few options, copy the data into notepad then then paste it back which should get rid of the aprostrophe's or maybe add a column and try =SUBSTITUTE(A1,"'","") (there's an apostrophe between the quotes) I'm also thinking text to columns but I'm not able to get anything there to work.
Something else to note, my vlookup works fine on a column of text with leading apostrophe's so I'm not sure you would actually need to remove them.

maybe you should post a bit of your spreadsheet
 
Upvote 0
well, you have a few options, copy the data into notepad then then paste it back which should get rid of the aprostrophe's or maybe add a column and try =SUBSTITUTE(A1,"'","") (there's an apostrophe between the quotes) I'm also thinking text to columns but I'm not able to get anything there to work.
Something else to note, my vlookup works fine on a column of text with leading apostrophe's so I'm not sure you would actually need to remove them.

maybe you should post a bit of your spreadsheet


I am having difficulty attaching the file.
 
Upvote 0
Hi,

is it OK to send the spreadsheet to a personal account, i am new to this forum and I don't know how to attach documents on here
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top