06-29-2020 12:22 PM - last edited on 06-30-2020 08:44 AM by Eric Starker
I am having trouble with data in in a csv file that I created by exporting data from a Filemaker database. The Address field in the database is on multiple lines, but when I look at it in Excel, There are little rectangle characters that appear in the cell to indicate the line returns - see attached. I am then using that csv file to do a bulk upload of information to a Drupal-based content management system, and the rectangles still show up. I would love to have the line return happen in the CMS, but at the very least, is it possible to replace the characters in Excel?
06-29-2020 08:34 PM
I've asked the forum moderator to remove your image file--it contains the real names and emails of several real people. For future reference, always replace real names (or other confidential info) with anonymous ones...use the names of Disney characters or Star Wars parts, whatever.... If you're watching for replies and see this before the moderator, I'd suggest you go back to Edit that first post and remove the image. Replace it with one without those names.
To your question, I've often been able (in Word or Excel) to just copy the special text needs to be replaced and paste it into the Find and Replace dialog box. Depending on whether you're working with a Mac or an Windows computer, the keyboard short cuts Command+C or Ctrl+C can copy; Command+V or Ctrl+V will paste. Then use a space or other character to replace it.
06-30-2020 01:49 AM
You may try to copy this little rectangle and create a helper column in which you use a Substitute function
=SUBSTITUTE(D4,"Paste the rectangle",Char(10))
Char(10) pushes what comes next to a new line in the same cell.
You can then copy and paste values from the helper column to wherever you like.
Hope that helps