Phone listing (USAF)
- Thread starter ChristopherVermeer
- Start date
SirJB7
Excel Rōnin
Hi, ChristopherVermeer!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about your question...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
I hope you had no access to sites for uploading files... otherwise you're so nice...
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Phone%20listing%20%28USAF%29%20%28for%20ChristopherVermeer%20at%20chandoo.org%29.xlsx
If you suffer the CASFFML Luke M's illness (can't access shared files from my location), here are the formulas:
Column A: your imported text file
C2: =SI.ERROR(FECHA(2000+EXTRAE($A2;7;2);EXTRAE($A2;1;2);EXTRAE($A2;4;2));"") -----> in english: =IFERROR(DATE(2000+MID($A2,7,2),MID($A2,1,2),MID($A2,4,2)),"")
D2: =SI.ERROR(HORANUMERO(EXTRAE($A2;10;5));"") -----> in english: =IFERROR(TIMEVALUE(MID($A2,10,5)),"")
E2: =SI.ERROR(EXTRAE($A2;16;12);"") -----> in english: =IFERROR(MID($A2,16,12),"")
F2: =SI.ERROR(EXTRAE($A2;29;HALLAR(" ";$A2;SI.ERROR(HALLAR(",";$A2;29);29+LARGO("TOLL ")))-29);"") -----> in english: =IFERROR(MID($A2,29,SEARCH(" ",$A2,IFERROR(SEARCH(",",$A2,29),29+LEN("TOLL ")))-29),"")
G2: =SI.ERROR(EXTRAE($A2;HALLAR(" ";$A2;SI.ERROR(HALLAR(",";$A2;29);29+LARGO("TOLL ")))+1;HALLAR(" ";$A2;HALLAR(" ";$A2;SI.ERROR(HALLAR(",";$A2;29);29+LARGO("TOLL ")))+1)-HALLAR(" ";$A2;SI.ERROR(HALLAR(",";$A2;29);29+LARGO("TOLL ")))-1);"") -----> in english: =IFERROR(MID($A2,SEARCH(" ",$A2,IFERROR(SEARCH(",",$A2,29),29+LEN("TOLL ")))+1,SEARCH(" ",$A2,SEARCH(" ",$A2,IFERROR(SEARCH(",",$A2,29),29+LEN("TOLL ")))+1)-SEARCH(" ",$A2,IFERROR(SEARCH(",",$A2,29),29+LEN("TOLL ")))-1),"")
H2: =SI.ERROR(EXTRAE($A2;HALLAR("$";$A2);LARGO($A2)-HALLAR("$";$A2)+1);"") -----> in english: =IFERROR(MID($A2,SEARCH("$",$A2),LEN($A2)-SEARCH("$",$A2)+1),"")
I1: 0
I2: =SI.ERROR(SI(Y(ESNUMERO(C2);ESNUMERO(D2);LARGO(E2)-LARGO(SUSTITUIR(E2;"-";""))=2;F2<>"";G2<>"";H2<>"");VALOR(I1)+1;VALOR(I1));VALOR(I1)) -----> in english: =IFERROR(IF(AND(ISNUMBER(C2),ISNUMBER(D2),LEN(E2)-LEN(SUBSTITUTE(E2,"-",""))=2,F2<>"",G2<>"",H2<>""),VALUE(I1)+1,VALUE(I1)),VALUE(I1))
K2
2 : =SI.ERROR(INDICE(C:C;COINCIDIR(FILA()-1;$I:$I;0));"") -----> in english: =IFERROR(INDEX(C:C,MATCH(ROW()-1,$I:$I,0)),"")
Titles of Columns:
Text file / / Date / Time / Called Number / Destination / Duration / Amount / 0 / / Date / Time / Called Number / Destination / Duration / Amount
Columns C:I helper columns, columns K actual data.
Copy down formulas of row 2 as required.
Regards!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about your question...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
I hope you had no access to sites for uploading files... otherwise you're so nice...
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Phone%20listing%20%28USAF%29%20%28for%20ChristopherVermeer%20at%20chandoo.org%29.xlsx
If you suffer the CASFFML Luke M's illness (can't access shared files from my location), here are the formulas:
Column A: your imported text file
C2: =SI.ERROR(FECHA(2000+EXTRAE($A2;7;2);EXTRAE($A2;1;2);EXTRAE($A2;4;2));"") -----> in english: =IFERROR(DATE(2000+MID($A2,7,2),MID($A2,1,2),MID($A2,4,2)),"")
D2: =SI.ERROR(HORANUMERO(EXTRAE($A2;10;5));"") -----> in english: =IFERROR(TIMEVALUE(MID($A2,10,5)),"")
E2: =SI.ERROR(EXTRAE($A2;16;12);"") -----> in english: =IFERROR(MID($A2,16,12),"")
F2: =SI.ERROR(EXTRAE($A2;29;HALLAR(" ";$A2;SI.ERROR(HALLAR(",";$A2;29);29+LARGO("TOLL ")))-29);"") -----> in english: =IFERROR(MID($A2,29,SEARCH(" ",$A2,IFERROR(SEARCH(",",$A2,29),29+LEN("TOLL ")))-29),"")
G2: =SI.ERROR(EXTRAE($A2;HALLAR(" ";$A2;SI.ERROR(HALLAR(",";$A2;29);29+LARGO("TOLL ")))+1;HALLAR(" ";$A2;HALLAR(" ";$A2;SI.ERROR(HALLAR(",";$A2;29);29+LARGO("TOLL ")))+1)-HALLAR(" ";$A2;SI.ERROR(HALLAR(",";$A2;29);29+LARGO("TOLL ")))-1);"") -----> in english: =IFERROR(MID($A2,SEARCH(" ",$A2,IFERROR(SEARCH(",",$A2,29),29+LEN("TOLL ")))+1,SEARCH(" ",$A2,SEARCH(" ",$A2,IFERROR(SEARCH(",",$A2,29),29+LEN("TOLL ")))+1)-SEARCH(" ",$A2,IFERROR(SEARCH(",",$A2,29),29+LEN("TOLL ")))-1),"")
H2: =SI.ERROR(EXTRAE($A2;HALLAR("$";$A2);LARGO($A2)-HALLAR("$";$A2)+1);"") -----> in english: =IFERROR(MID($A2,SEARCH("$",$A2),LEN($A2)-SEARCH("$",$A2)+1),"")
I1: 0
I2: =SI.ERROR(SI(Y(ESNUMERO(C2);ESNUMERO(D2);LARGO(E2)-LARGO(SUSTITUIR(E2;"-";""))=2;F2<>"";G2<>"";H2<>"");VALOR(I1)+1;VALOR(I1));VALOR(I1)) -----> in english: =IFERROR(IF(AND(ISNUMBER(C2),ISNUMBER(D2),LEN(E2)-LEN(SUBSTITUTE(E2,"-",""))=2,F2<>"",G2<>"",H2<>""),VALUE(I1)+1,VALUE(I1)),VALUE(I1))
K2
2 : =SI.ERROR(INDICE(C:C;COINCIDIR(FILA()-1;$I:$I;0));"") -----> in english: =IFERROR(INDEX(C:C,MATCH(ROW()-1,$I:$I,0)),"")Titles of Columns:
Text file / / Date / Time / Called Number / Destination / Duration / Amount / 0 / / Date / Time / Called Number / Destination / Duration / Amount
Columns C:I helper columns, columns K
actual data.Copy down formulas of row 2 as required.
Regards!
ChristopherVermeer
New Member
Thanks for your help! And i will post properly next time.
SirJB7
Excel Rōnin
Hi, ChristopherVermeer!
I see you've updated the first post so as to keep indentation, even it's more clear to be read, please note that the parsing & extracting formulas rely on the firstly post text, so if your imported text file will appear in column A as originally, the uploaded file and the posted formulas will work; but if your imported text file will look like the edited post, even if you could keep the formula structure you'd have to adjust all starting and ending positions.
Regards!
I see you've updated the first post so as to keep indentation, even it's more clear to be read, please note that the parsing & extracting formulas rely on the firstly post text, so if your imported text file will appear in column A as originally, the uploaded file and the posted formulas will work; but if your imported text file will look like the edited post, even if you could keep the formula structure you'd have to adjust all starting and ending positions.
Regards!
SirJB7
Excel Rōnin
@Luke M
Hi!
So it was you... and me blaming the poor OP!
However the clarification at last post might be useful for him, just in case he decides to import or paste or anything from the original file in a formatted way.
Regards!
PS: It was nice to find out the limits with all the data left aligned, the nicest was the Destination field with the comma, then appeared LOCAL without comma so entered following blank in the game, but (as the old dog uses to say) then TOLL FREE came in scratching next blank... and so on.
Hi!
So it was you... and me blaming the poor OP!
However the clarification at last post might be useful for him, just in case he decides to import or paste or anything from the original file in a formatted way.
Regards!
PS: It was nice to find out the limits with all the data left aligned, the nicest was the Destination field with the comma, then appeared LOCAL without comma so entered following blank in the game, but (as the old dog uses to say) then TOLL FREE came in scratching next blank... and so on.