Converting special characters

%3CLINGO-SUB%20id%3D%22lingo-sub-1497529%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20special%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1497529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711666%22%20target%3D%22_blank%22%3E%40DKPorter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20asked%20the%20forum%20moderator%20to%20remove%20your%20image%20file--it%20contains%20the%20real%20names%20and%20emails%20of%20several%20real%20people.%20For%20future%20reference%2C%20always%20replace%20real%20names%20(or%20other%20confidential%20info)%20with%20anonymous%20ones...use%20the%20names%20of%20Disney%20characters%20or%20Star%20Wars%20parts%2C%20whatever....%26nbsp%3B%20If%20you're%20watching%20for%20replies%20and%20see%20this%20before%20the%20moderator%2C%20I'd%20suggest%20you%20go%20back%20to%20Edit%20that%20first%20post%20and%20remove%20the%20image.%20Replace%20it%20with%20one%20without%20those%20names.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20your%20question%2C%20I've%20often%20been%20able%20(in%20Word%20or%20Excel)%20to%20just%20copy%20the%20special%20text%20needs%20to%20be%20replaced%20and%20paste%20it%20into%20the%20Find%20and%20Replace%20dialog%20box.%20Depending%20on%20whether%20you're%20working%20with%20a%20Mac%20or%20an%20Windows%20computer%2C%20the%20keyboard%20short%20cuts%20Command%2BC%20or%20Ctrl%2BC%20can%20copy%3B%20Command%2BV%20or%20Ctrl%2BV%20will%20paste.%26nbsp%3B%20Then%20use%20a%20space%20or%20other%20character%20to%20replace%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1497927%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20special%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1497927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711666%22%20target%3D%22_blank%22%3E%40DKPorter%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%3C%2FP%3E%0A%3CP%3EYou%20may%20try%20to%20copy%20this%20little%20rectangle%20and%20create%20a%20helper%20column%20in%20which%20you%20use%20a%20Substitute%20function%3C%2FP%3E%0A%3CP%3E%3DSUBSTITUTE(D4%2C%22Paste%20the%20rectangle%22%2CChar(10))%3C%2FP%3E%0A%3CP%3EChar(10)%20pushes%20what%20comes%20next%20to%20a%20new%20line%20in%20the%20same%20cell.%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20copy%20and%20paste%20values%20from%20the%20helper%20column%20to%20wherever%20you%20like.%3C%2FP%3E%0A%3CP%3EHope%20that%20helps%3C%2FP%3E%0A%3CP%3ENabil%20Mourad%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1496801%22%20slang%3D%22en-US%22%3EConverting%20special%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1496801%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20trouble%20with%20data%20in%20in%20a%20csv%20file%20that%20I%20created%20by%20exporting%20data%20from%20a%20Filemaker%20database.%20The%20Address%20field%20in%20the%20database%20is%20on%20multiple%20lines%2C%20but%20when%20I%20look%20at%20it%20in%20Excel%2C%20There%20are%20little%20rectangle%20characters%20that%20appear%20in%20the%20cell%20to%20indicate%20the%20line%20returns%20-%20see%20attached.%20I%20am%20then%20using%20that%20csv%20file%20to%20do%20a%20bulk%20upload%20of%20information%20to%20a%20Drupal-based%20content%20management%20system%2C%20and%20the%20rectangles%20still%20show%20up.%20I%20would%20love%20to%20have%20the%20line%20return%20happen%20in%20the%20CMS%2C%20but%20at%20the%20very%20least%2C%20is%20it%20possible%20to%20replace%20the%20characters%20in%20Excel%3F%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1496801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1499025%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20special%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1499025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F41707%22%20target%3D%22_blank%22%3E%40Eric%20Starker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEric%20--%20a%20quick%20FYI.%20When%20I%20clicked%20on%20the%20link%20to%20the%20%22code%20of%20conduct%22%20I%20got%20this%20message.%20You%20must%20be%20referring%20to%20the%20true%20insider's%20version.%20I%20presume%20the%20OP%20would%20get%20this%20too......%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1593533244798.png%22%20style%3D%22width%3A%20617px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202203i14B53C3F841AE5B6%2Fimage-dimensions%2F617x145%3Fv%3D1.0%22%20width%3D%22617%22%20height%3D%22145%22%20title%3D%22mathetes_0-1593533244798.png%22%20alt%3D%22mathetes_0-1593533244798.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1498922%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20special%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1498922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711666%22%20target%3D%22_blank%22%3E%40DKPorter%3C%2FA%3E%26nbsp%3BHello!%20We%20have%20removed%20the%20image%20file%20due%20to%20including%20real%20names%20and%20email%20addresses.%20As%20per%20our%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fcommunity-guidelines%2Fmicrosoft-tech-community-code-of-conduct%2Fm-p%2F31390%22%20target%3D%22_self%22%3Ecode%20of%20conduct%3C%2FA%3E%2C%20please%20don't%20share%20personal%20or%20private%20information%20here.%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1499059%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20special%20characters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1499059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BSorry%20about%20that%2C%20link%20is%20fixed%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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?   

5 Replies

@DKPorter 

 

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.

@DKPorter 

Hi

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

Nabil Mourad

 

@DKPorter Hello! We have removed the image file due to including real names and email addresses. As per our code of conduct, please don't share personal or private information here. Thank you. 

@Eric Starker 

 

Eric -- a quick FYI. When I clicked on the link to the "code of conduct" I got this message. You must be referring to the true insider's version. I presume the OP would get this too......

mathetes_0-1593533244798.png

 

@mathetes Sorry about that, link is fixed now.