Data formatting issue

[Lmallow1542]

Hello I have 7 columns of data spanning 3077 rows and I have concenated it to one column to read as an address with phone number and website..each of these having a title in the concenate formula. I need to transfer this data to a CVS form to upload into a map however when I try it reads data as a formula. I need to know how to get it to read as text while keeping the formatting. I know i can paste it into notepad and this will turn it to text then I can paste it back however doing this 3077 times is something I want to avoid. I have done it once however I don't know the exact steps as I tried million things and somehow got it to work but I cant seem to recreate this miracle! Please help!!

 

[DeficientOptimism]

Couldn't you just copy and paste the column with the formula as values and save as CSV? Save down the workbook with the formula for the future?

 

[Lmallow1542]

Couldn't you just copy and paste the column with the formula as values and save as CSV? Save down the workbook with the formula for the future?

If I do it removes formatting

 

[DeficientOptimism]

Okay - try this.

Save a copy of the file in xlsx first to revert back.

Open up a copy, paste the formula column as values. Now, next to the formula column you have pasted as values place an apostrophe at the start of all the cells.

=‘'’&‘(cellyouhavepastedasvalues)’

In the brackets reference the cell you have pasted as values and drag down to the bottom of your data set. Now you should have an apostrophe at the start of each cell of your concatenated data.

Paste this new column now as values, delete the old column without the apostrophe and see if this works?

 

[RobVos]

Copy column P. Paste values into column Q. Copy column P again. Paste formats into column Q. The formatting will then be there and not formulas.