Text Data Manipulation from different dumps
Hi Guys we receive data dumps of personnel from our different applications (165 of them) in different formats. I need a formula(s) to convert any of the 3 examples into the same format.
I need the formula to manipulate the date to read SURNAME_Personnel number E.g. SMITH_0333
The common formats we get are listed here http://i.imgur.com/vCr2b.png or in the attached spreadsheet
Please can somebody help me Thorrrr
One way, but it requires a Helper column.
In your example:
F2: =IF(AND(B2="",C2="",D2=""),A2,IF(AND(C2="",D2=""),A2&" "&TEXT(B2,"0000"),A2&IF(B2<>""," "&B2,"")&" "&C2&" "&TEXT(D2,"0000")))
G2: =RIGHT(SUBSTITUTE(SUBSTITUTE(F2," ","_",LEN(F2)-LEN(SUBSTITUTE(F2," ","")))," ","^",LEN(F2)-LEN(SUBSTITUTE(F2," ",""))-1),(LEN(SUBSTITUTE(SUBSTITUTE(F2," ","_",LEN(F2)-LEN(SUBSTITUTE(F2," ","")))," ","^",LEN(F2)-LEN(SUBSTITUTE(F2," ",""))-1))-FIND("^",SUBSTITUTE(SUBSTITUTE(F2," ","_",LEN(F2)-LEN(SUBSTITUTE(F2," ","")))," ","^",LEN(F2)-LEN(SUBSTITUTE(F2," ",""))-1))))
Copy both formulae down. I tried to combine the two but it just got too long.
Regards, TMS
Last edited by TMS; 01-12-2012 at 03:40 PM. Reason: Amended first formula
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I'm sure it can be improved upon. The first formula gets all versions into a consistent format. The second fomula extracts the surname and staff number.
However, perhaps this is something you can work with until someone comes up with a better approach.
Regards, TMS
Cheers TM
I have made a slight error i need the double barrelled name to by added together with the - striped out sorry!!!!! doh
So David Umber-Green 1234 would be UMBERGREEN_1234
How would i achieve that ?
F2: =SUBSTITUTE(IF(AND(B2="",C2="",D2=""),A2,IF(AND(C2="",D2=""),A2&" "&TEXT(B2,"0000"),A2&IF(B2<>""," "&B2,"")&" "&C2&" "&TEXT(D2,"0000"))),"-","")
G2 remains the same.
Regards, TMS
in one
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1)," "&TRIM(RIGHT(SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1)," ",REPT(" ",50)),25)),"")," ",REPT(" ",50)),25))&" "&TEXT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1)," ",REPT(" ",50)),25)),"0000")
Last edited by martindwilson; 01-12-2012 at 07:12 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
@Martin: neat! No idea what that does, could you clarify, please? And note the "oops" a couple of posts back
Regards, TMS
its not quite right but i think this is
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2),"-","")," "&TRIM(RIGHT(SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2)," ",REPT(" ",50)),25)),"")," ",REPT(" ",50)),25))&"_"&TEXT(TRIM(RIGHT(SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2)," ",REPT(" ",50)),25)),"0000")
TEXT(TRIM(RIGHT(SUBSTITUTE(TRIM(A2&" "&B2&" "&C2&" "&D2)," ",REPT(" ",50)),25)),"0000")
gets the number
then substitute it back in TRIM(A2&" "&B2&" "&C2&" "&D2) with "" to remove it
then extract the last word with trim(right(substitute(all that stuff,repeat(" ",50)),25)
oh and somwhere in there i also substituted - with "" then add them together
Perhaps this???
=TRIM(RIGHT(SUBSTITUTE(TRIM(TEXT(A1,"0000;;;@")&" "&TEXT(B1,"0000;;;@")&" "&TEXT(C1,"0000;;;@")&" "&TEXT(D1,"0000;;;@"))," ",REPT(" ",250)),500))
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Well, I thought, no, knew, there would be better/shorter versions but I am a little embarrassed now.
But ... I think, an added refinement to Haseeb's version to remove the "-" and change the " " to "_" completes the picture:
Row 2: =SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(TEXT(A2,"0000;;;@")&" "&TEXT(B2,"0000;;;@")&" "&TEXT(C2,"0000;;;@")&" "&TEXT(D2,"0000;;;@"))," ",REPT(" ",250)),500)),"-","")," ","_")
Regards, TMS
yep i didnt think of this 0000;;;@ i tried it similarly with just "0000" and got 0000_0000so i gave up on that approach good call haseeb ,i'll have to go back and learn a few formats!
Can I just thank you all of you for your great help some very clever people and you have made my life easier well done
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks