+ Reply to Thread
Results 1 to 16 of 16

Extrac Number and Text in string

  1. #1
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    Extrac Number and Text in string

    Hi.

    I have a file with a field filled based users (this file is exported another sistem), but There is no default(logic pattern) in filling, becouse this is very hard extrac number and text in string.

    In my file has column A (data exported), i need get this data in column A and split in tow columns B and C, look my file, there is a result (column B and C)

    Thank you!!
    Attached Files Attached Files
    "No xadrez nem sempre a menor dist?ncia entre dois pontos ? uma linha reta" G. Kasparov.

    If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select b from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Extrac Number and Text in string

    maybe so
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    Re: Extrac Number and Text in string

    You can adjust this part(look my image file)?
    Attached Images Attached Images
    Last edited by marreco; 04-04-2017 at 02:55 PM.

  4. #4
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Extrac Number and Text in string

    Replace this line
    Please Login or Register  to view this content.
    with this line
    Please Login or Register  to view this content.
    No, no, not only that...

  5. #5
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Extrac Number and Text in string

    try so
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    Re: Extrac Number and Text in string

    You realy help, thanks

    if you have patience and want adjust this part, look result (post #3, I edit after run based post #5) after run you code.
    Last edited by marreco; 04-04-2017 at 03:35 PM.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extrac Number and Text in string

    Try these formulas...
    B2=IFERROR(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND("#",SUBSTITUTE(A2,"-","#",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-2),"00",""),"-","|"),"")
    C2=IFERROR(MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+2,99),A2)
    both copied down as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    Re: Extrac Number and Text in string

    ok, but this way happing some strange like code in a monment.
    Column A---------------------------------------------Column B
    3535483 - Qtd. Maior que Faturada - Sobra 3535483 | Qtd. Maior que Faturada
    3408756 - Qtd. Maior que Faturada - Sobra 3408756 | Qtd. Maior que Faturada
    Would be
    Column A---------------------------------------------Column B--------------------------------Column C
    3535483 - Qtd. Maior que Faturada - Sobra 3535483 Qtd. Maior que Faturada
    3408756 - Qtd. Maior que Faturada - Sobra 3408756 Qtd. Maior que Faturada

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extrac Number and Text in string

    Quote Originally Posted by marreco View Post
    ok, but this way happing some strange like code in a monment.
    Who was that addressed to?

  10. #10
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    Re: Extrac Number and Text in string

    for you FDibbins , i try your formula, but in somewhere in the data it was like no code

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extrac Number and Text in string

    aahh OK, you have some text in there that also includes -
    3535483 - Qtd. Maior que Faturada - Sobra

    Because you have some cases of double numbers, I was testing to see how many - where in the cell. if there is - inside the text, that messes that around

    will the numbers always be 7 digits?

  12. #12
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    Re: Extrac Number and Text in string

    the users fill this datas inside textbody, but this guys fill non pattern.
    this is based 1 NF (Invoice of merchandise)
    case 1 = 3535483
    case 2 = 3535483-3
    case 3 = 003535483
    is possible has multiples NF
    case 1 = 3535483-3579078-3577334 or 3535483/3579078/3577334
    case 2 = 3535483-3-3579078-3-3577334-3 or 3535483-3/3579078-3/3577334-3
    case 3 = 003535483-003579078-003577334 or 003535483/003579078/003577334

    The people who fill this field of the system do not use a logical one, they do it anyway.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Extrac Number and Text in string

    My suggestion should still work for numbers with - between them, but it will be thrown off if there are - inside the text anywhere

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extrac Number and Text in string

    marreco
    There are still few different results in col.B that I don't understand the logic...
    Please Login or Register  to view this content.
    Last edited by jindon; 04-05-2017 at 01:52 AM. Reason: Fixed a bug.

  15. #15
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Extrac Number and Text in string

    or this
    Please Login or Register  to view this content.

  16. #16
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    Re: Extrac Number and Text in string

    Hi nilem, perfect!!!

    marreco
    There are still few different results in col.B that I don't understand the logic...
    no there logic, becouse users fill one way today, tomorrow another way and on and on, but your do it a great code, this realy help me.

    Thank you very much!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-28-2015, 09:07 AM
  2. extrac data from different sheets by sheet name and copy the workbook names
    By ripple in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-31-2013, 11:26 AM
  3. [SOLVED] Need to limit the number of decimal places of a number in a text string
    By Turbo Dog in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-11-2013, 07:47 PM
  4. [SOLVED] How to Extract A Number from String if Text within the String Equals XYZ
    By tyrsfury in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2012, 03:30 PM
  5. Converting text string to number string
    By cdworin in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-18-2010, 06:31 AM
  6. Replies: 6
    Last Post: 07-29-2008, 03:23 PM
  7. [SOLVED] Splitting a text string into string and number
    By mcambrose in forum Excel General
    Replies: 4
    Last Post: 02-21-2006, 11:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1