Looking for Perfect Formula for data cleansing - Pls Help

Jithesh Joseph · 2020-07-20T12:16:28Z

I have an excel sheet that contains registrations list of our event. Depending upon the country you are registering from the length of the form gets adjusted. Bcs of this reason when I created a list of final registration, data seems to be jumbled up a bit. For eg: C stands for Column and R stands for Rows... Under Column 1 (First Name), in Rows 1-3 I have First Names. But from 4 - 6, it’s Email, Reg_subjectline and Apps. There are at least 20 Columns and in each Columns data is arranged in this manner but they are not equally sequenced in rows. My requirement is to get only emails in one single column C1, first names in C2, phone in C3, for all registrants... any formula that u can recommend wud be really helpful....

Page 2 / 2 Prev

General Excel Questions & Answers

Last Post by Jithesh Joseph 6 years ago

18 Posts

2 Users

0 Reactions

2,413 Views

RSS

Jithesh Joseph

(@jithu-joval)

Posts: 10

Active Member

Topic starter

Hi Mynda,

It did work, but i had white spaces in between the texts also. Unfortunately, Trim under Transform tab didnt remove it. Any other ways?

Posted : 28/07/2020 12:18 am

Mynda Treacy

(@mynda)

Posts: 4783

Member Admin

Please start a new topic and upload an Excel file with a sample of the problem data. There are probably some non-printing characters in the data that you need to identify so you can tell Power Query what to remove.

Mynda

Posted : 28/07/2020 3:24 am

Jithesh Joseph

(@jithu-joval)

Posts: 10

Active Member

Topic starter

Okay Mynda.

Posted : 29/07/2020 12:38 am

Page 2 / 2 Prev

26 Forums
7,428 Topics
32.7 K Posts
9 Online
36.9 K Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed