convert a value to a text incl apostrophe within the cell

[Guest]

Dear experts,
I need a workaround BW reporting and the use of pivot tables in Excel. BW
downloads e.g. material numbers incl an apostrophe e.g. '112233. However,
this apostrophe is only shown when you press F2, i.e. this is a text format.
From another excel file I have the material numbers as values e.g. 112233. I
need to convert this 112233 to '112233, but in a way so the apostrophe is
only shown when you press F2. I have tried several formulas e.g.
=concatenate, =text etc. and can easily get the '112233 with the apostrophe
shown, but not as "real" text.

Does really appreciate you help in this, thanks a 1000 times

Best
Jakob

 

[Beege]

Dear experts,
I need a workaround BW reporting and the use of pivot tables in Excel. BW
downloads e.g. material numbers incl an apostrophe e.g. '112233. However,
this apostrophe is only shown when you press F2, i.e. this is a text
format.
From another excel file I have the material numbers as values e.g. 112233.
I
need to convert this 112233 to '112233, but in a way so the apostrophe is
only shown when you press F2. I have tried several formulas e.g.
=concatenate, =text etc. and can easily get the '112233 with the
apostrophe
shown, but not as "real" text.

Does really appreciate you help in this, thanks a 1000 times

Best
Jakob

Jakob

First insert a helper column.
=CHAR(39)&A1
Copy down
(should see '123344)
Select helper column
Copy
Paste special (in the same spot)/values
Search/replace ' with ' replace all - worked for me. HTH for you.

Beege

 

[Beege]

Jakob

First insert a helper column.
=CHAR(39)&A1
Copy down
(should see '123344)
Select helper column
Copy
Paste special (in the same spot)/values
Search/replace ' with ' replace all - worked for me. HTH for you.

Beege

Or check out the code on this page...

http://msdn.microsoft.com/library/d...c_xl2003_ta/html/OfficeTipsWhenUsingExcel.asp

Beege