I have the attached spreadsheet that has 176K of addresses. The cell contains data that is actually Address Line 1 and Address Line 2. I need to separate these. I BELIEVE there is an unprintable character that separates most of them -- maybe a CRLF or something like that.
Please see the attachment for examples...
Thanks folks! Cust Address with unprintable characters.xlsx
try this...put these in and copy down...this does not take into account cells that don't have the CHAR(10)....Linefeed, by the way....
in C2
in D2
Please Login or Register to view this content.
Please Login or Register to view this content.
Ernest
Please consider adding a * if I helped
Nothing drives me crazy - I'm always close enough to walk....
in C2:
=LEFT(B2,SEARCH(CHAR(10),B2,1)-1)
in D2
=SUBSTITUTE(B2,C2,"")
and copy down.
I'm not sure about row 1 - I dont think that has a hidden character.
When this data is separated, where do you want the first line or second line to appear.
How do you want the data to appear post removal?
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thinking about it, you might be able to do this with the texttocolumns tool, select delimited, other and then in the box for other put ctrl+j to get the linefeed character.
That would help deal with the entries without a linefeed better as well I think.
Perfect -- I will apply this to the other 175K! You Rock!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks