Data cleansing to remove blank space and "|"

Hi Experts,

Following raw- data in Excel 2010 running over thousands of rows, but in one cell only ( A1, A2,A3, and so on)
I need to put in useful tables for purpose. If I use command/text to column/delimited it works and remove using find/replace command to get rid of "|".
Issue I face is for Email. Because of blank space left after end of email address up to "|" , see below. How do I get rid of it ?
It is observed that "Underline" in the email-address remains extended up to deleted "|"


0345333|REEM FLOYED |FLINT TRAVEL BUILDING, |GROUND FLOOR, 529 TOORAK ROAD |TOORAK |3242 |Victoria |Australia |9783 6366 ||[email protected] |


Any suggestion is welcomed,

Karnik

maybe this:

Please Login or Register  to view this content.

and after you can use Text2Columns with space delimiter

Hello Karnik,

I assume you are using the pipe symbol (|) for Text to Column conversion.

You mention Find & Replace - I suggest you use it, but Find " |" and Replace with "|".
That is to say, search for SPACE + |. This will get rid of all the unwanted spaces at the end of "fields".

Then do your usual thing.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".