Hi,
Good day!
I'm getting a file with a format that is hard to use formula or add to another sheet. I would like to change it to my preferred format so I could integrate it with my tables. I'v attached the file with the format I'm receiving and the preferred format. Is this possible to do in Excel? Please help.
Last edited by thebrucekt; 11-07-2017 at 04:25 AM.
Hi thebrucekt,
Because you have Excel 2016 you can use Power Query to convert your data. See sheet 3 where I did a little PQ on it. Click on the data tab and view query. See if this will work for you.
Attached deleted as per OP request.
Last edited by MarvinP; 11-07-2017 at 09:16 AM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
hi there. another alternative is using formulas. in G5:
=IFERROR(MID(C5,FIND("Name: ",C5)+6,FIND(" (",C5)-FIND("Name: ",C5)-6),"")
in H5:
=IFERROR(--MID(C5,FIND("(",C5)+1,FIND(" records",C5)-FIND("(",C5)-1),"")
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hi Benishiryo,
how if I don't want any spaces between the rows? what can i edit?
in G5:
=IFERROR(MID(C5,FIND("Name: ",C5)+6,FIND(" (",C5)-FIND("Name: ",C5)-6),"")
in H5:
=IFERROR(--MID(C5,FIND("(",C5)+1,FIND(" records",C5)-FIND("(",C5)-1),"")
Last edited by thebrucekt; 11-07-2017 at 04:56 AM.
Which proposed solution are you questioning?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGw,
with this:
how if I don't want any spaces between the rows? what can i edit?
in G5:
=IFERROR(MID(C5,FIND("Name: ",C5)+6,FIND(" (",C5)-FIND("Name: ",C5)-6),"")
in H5:
=IFERROR(--MID(C5,FIND("(",C5)+1,FIND(" records",C5)-FIND("(",C5)-1),"")
I would use the PowerQuery solution!
i agree with AliGW, but i do understand there are all kinds of reasons not to use a particular solution. in cell G5, change all cell reference C5 to be:
INDEX(C:C,5+(ROWS($G$5:G5)-1)*2)
so:
Formula:
Please Login or Register to view this content.
H5 would be:
Formula:Please Login or Register to view this content.
I5:
=INDEX(D:D,5+(ROWS(I$5:I5)-1)*2)
copy across to column J
All I can say is WOW!
Thank you so much to MarvinP , AliGw and to benishiryo !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks