APOSTROPHES EVERYWHERE

[paquirl]

I was given a workbook to do some vlookups, and the cells are text, with the number i need but with an apostrophe in the front. It's this way on two columns I'm trying to concatenate. I am guessing the apostrophes are the problem? Help?

 

[dreid1011]

Are what problem? What happens when you try to concatenate the values? We need more information and sample data showing what the problem is and desired result.

 

[paquirl]

Concatenate and =right do not work , just the formula shows as result.

'636425

'97504

 

[dreid1011]

This is what I get with apostrophes in A1 and B1.

Book1 (version 1) 1-25-2024.xlsx
	A	B	C
1	636425	97504	63642597504
Sheet8

Cell Formulas
Range	Formula
C1	C1	=CONCAT(A1,B1)

 

[6StringJazzer]

Please explain what result you are trying to get, and what formula you are trying to use.

If the first character in a cell is an apostrophe, this forces Excel to interpret that cell as text rather than a number. But without more detail about your data and what you want to do it's hard to give you the best solution.

Are you allowed to edit the data? My first impulse would be to convert them to numbers. One way:

1. Type 0 into any unused cell
2. CTRL+c to copy the 0
3. Select the cells with the apostrophes
4. Paste Special, select the "Add" option
5. Values will be converted to numbers

I don't understand how you are using VLOOKUP to concatenate data. I also don't understand why you would concatenate numbers.

 

[etaf]

will DATA> text to columns work to remove the ' and leave as a number
delimited with '
in the column fo ryou

 

[paquirl]

i need the numbers combined into a single 11 digit number that i can then run vlookups against. the formulas are showing as the result

 

[dreid1011]

i need the numbers combined into a single 11 digit number that i can then run vlookups against. the formulas are showing as the result

If you go to the Formulas tab, is "Show Formulas" selected?

 

[paquirl]

will DATA> text to columns work to remove the ' and leave as a number
delimited with '
in the column fo ryou

yes that removed the apostrophes in both a1 and b1, however when i run concat in c1, it still shows me the formula '=CONCATENATE(a1,b1) with an apostrophe in front

 

[paquirl]

If you go to the Formulas tab, is "Show Formulas" selected?
View attachment 105808

negative