I have a question that I hope someone might be able to help with, I have an export from a system thats poor to say the least, it comes out into Excel as 3 columns, A is a reference, B is the persons details, C is a mix of email, phone, mobile and home numbers (with ; as delimiters)
the format of B is e: emailaddress m: mobilenumber h: housenumber
the house number and mobile sometimes has spaces etc. what I need to do is separate the C column which I can do with text to Column using ; delimiter but as its not alway e,m,h but could be h,e,m or others.
What I need is new Columns at the end or new tab that looks at the separate columns and just shows me Ref, Person, Mobile or Home
eg:
| Column A | Column B | Column C | Column D | Column E | Column F |
|---|---|---|---|---|---|
| ITD123 | Mr xyx | e: [email protected]; h: 01274; m: 7845 | |||
| MIN243 | Mrs eye | h: 08774; m: 5485;e: [email protected]; |
using Text to Colums I get:
| Column A | Column B | Column C | Column D | Column E | Column F |
|---|---|---|---|---|---|
| ITD123 | Mr xyx | e: [email protected]; h: 01274; m: 7845 | e:[email protected] | h: 01274 | m: 7845 |
| MIN243 | Mrs eye | h: 08774; m: 5485;e: [email protected]; | h: 08774 | m: 5485 | e: [email protected] |
Anyone know how I can do this without having to manually copy and paste?
I have tried Vlookup and IF but I am not a excel user. so I am a bit lost
Once I have this, I need to export the tab to CSV