Lmallow1542

New Member
Joined
Jan 25, 2022
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
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!!
 

Attachments

  • example excel sheet for question.PNG
    example excel sheet for question.PNG
    21.8 KB · Views: 17

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top