# 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?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
=concatenate("'",A2,"'")

Copy down the formula
0
Commented:
Assuming that your data is in column A and start from second row. Use the above formula or
="'"&A2&"'"

Copy down
0
Author Commented:
Thanks, but
it returned 0.
0
Commented:
Where is your range of data?
0
Commented:
It returned zero perhaps because there mohht be no data in A2
0
Commented:
What are the cell format is it text or number?
0
Commented:
Can you try using...

="'"&trim(a1)&"'"

Saurabh...
0
Author Commented:
I tried the second one as well. It still returned 0. There is data in A2.
0
Commented:
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)&"'"
0
Author Commented:
="'"&trim(a1)&"'" worked to a new column, but not the original column. How can I do it on the original column ?Thx.
0
Commented:
Once it works on a new column-->Copy the data and then go back to your original column and right click-->paste special-->values
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
=CONCATENATE("'",A2,"'")  Worked for me
0
Commented:
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..
0
Older than dirtCommented:
In a macro

Range(A1").Formula = "''" & Range("A1").Formula & "'"
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.