tanj1035
asked on
excel, add ' ' to the text
Hi Experts,
In my column showing text
A
B
C
If I want to add ' ' to them , like this
'A'
'B'
'C'
How do I do it ? Thanks?
In my column showing text
A
B
C
If I want to add ' ' to them , like this
'A'
'B'
'C'
How do I do it ? Thanks?
Assuming that your data is in column A and start from second row. Use the above formula or
="'"&A2&"'"
Copy down
="'"&A2&"'"
Copy down
ASKER
Thanks, but
it returned 0.
it returned 0.
Where is your range of data?
It returned zero perhaps because there mohht be no data in A2
What are the cell format is it text or number?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried the second one as well. It still returned 0. There is data in A2.
you need to apply this formula in b2..rather then a2..the reason its returning zero because you are creating a cyclic formula as in referring the same cell in the formula where you are applying the formula..
so what i mean if A2=A then apply this formula in B2="'"&trim(a2)&"'"
so what i mean if A2=A then apply this formula in B2="'"&trim(a2)&"'"
ASKER
="'"&trim(a1)&"'" worked to a new column, but not the original column. How can I do it on the original column ?Thx.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Alternatively if you just want the visual display and don't won't quotes their you can press ctrl+1 on the cell then custom and apply this formatting-->'@'
This will show visual display of values have single quotes but the actual value remains without quotes..
This will show visual display of values have single quotes but the actual value remains without quotes..
In a macro
Range(A1").Formula = "''" & Range("A1").Formula & "'"
Range(A1").Formula = "''" & Range("A1").Formula & "'"
Copy down the formula