+ Reply to Thread
Results 1 to 15 of 15

get Numbers From Alphanumeric Text

  1. 06-30-2010, 08:11 AM #1
    mkim41
    mkim41 is offline
    Registered User
    Join Date
    06-30-2010
    Location
    Syracuse, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Unhappy get Numbers From Alphanumeric Text

    Hey...

    I got a challenge at my work about Get Numbers From Alphanumeric Text in Excel...

    Every day, my department is going to receive plenty of data from several different departments...

    The excel sheet contains data and columns...

    Everything is fine but the problem is Merge Repair Scheme (Row J)

    Though, we only need #, but every time we receive # and specific merged data...

    Original Excel Data sheet is just like this...

    Merge Repair Scheme
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule Details Repair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    Repair 02 - Tube Nut Thrust Wire InstallationRepair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    06 -
    Repair 06 -
    Repair 06 -
    Repair 06 -
    Repair 06 -
    Repair 01 - Apply The Antigalling CompoundRepair 02 - Tube Nut Thrust Wire InstallationRepair 14 - Tube/Duct Assembly And Detail Replacements Repairs (Vendor Application)Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    Repair 14 - Tube/Duct Assembly And Detail Replacements Repairs (Vendor Application)Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsNA
    Repair 21 - Gold Nickel Brazed Repair (Vendor Application)Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsNA
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    Repair 01 - Apply The Antigalling CompoundRepair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 41 - SST Tube Assemblies, Tube & Detail Replacement, Nickel-Braze Repair (Vendor Application)NA
    Repair 14 - Tube/Duct Assembly And Detail Replacements Repairs (Vendor Application)Repair 01 - Apply The Antigalling CompoundRepair 02 - Tube Nut Thrust Wire InstallationRepair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule
    VRS 3759 - Detail Replacement Repair (Replace Heat Shields Only)NA
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule Details.Repair 28 - SST Silver Brazed Ext Tube Assy, Tube Sect. & Detail Replacement Repair (Vendor Application).NA
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule Details.Repair 28 - SST Silver Brazed Ext Tube Assy, Tube Sect. & Detail Replacement Repair (Vendor Application).NA
    Repair 28 - SST Silver Brazed Ext Tube Assy, Tube Sect. & Detail Replacement Repair (Vendor Application).NA


    However,
    All I need is
    For example,

    1st row, just 11- 25-

    2nd row, I need just 02-, 11-

    3nd row, just need 11-, 25-,

    4th row, 06-


    Now we change every single data manually by using find and replace option…

    The problem is, as time goes by, the data are growing and would be going beyond hundreds...

    So, I need a VBA code to automatically revise this whole description to just number that I need…

    Actually, I'm not good at VBA material to make formula...Actually, this is the first time...

    so, anyone knows any sugestion, let me know...

    Thanks...
    Reply With QuoteRegister To Reply
  2. 06-30-2010, 08:47 AM #2
    pike
    pike is offline
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: get Numbers From Alphanumeric Text

    Hi mkim41
    Welcome to the forum
    Try something like this..
    Please Login or Register  to view this content.
    you will have to adjust the input range
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs
    Reply With QuoteRegister To Reply
  3. 06-30-2010, 09:09 AM #3
    werfnort
    werfnort is offline
    Registered User
    Join Date
    08-29-2007
    Posts
    47

    Re: get Numbers From Alphanumeric Text

    Aw dang - leave it to Moderators to beat me to the punch.

    I'm still posting mine though, so OP can pick and choose.

    Mine a little more basic, just loops through and picks out on "numbers," which you can edit to include whatever you want.

    It will automatically search all of Column J.

    Also, added a "separator" string, so you can edit that to make it a comma, hyphen, whatever you would like.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 06-30-2010, 09:11 AM #4
    pike
    pike is offline
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: get Numbers From Alphanumeric Text

    never to late,many ways to skin a banana
    Reply With QuoteRegister To Reply
  5. 07-01-2010, 03:14 AM #5
    mkim41
    mkim41 is offline
    Registered User
    Join Date
    06-30-2010
    Location
    Syracuse, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Question Re: get Numbers From Alphanumeric Text

    Thanks for your help, Pike...

    But maybe it dosen't work or I don't know how to use this function...

    could you give me the instruction how to put and use this function from the beginning to the end...

    I'm a true beginner...

    Thanks...
    Reply With QuoteRegister To Reply
  6. 07-01-2010, 03:15 AM #6
    mkim41
    mkim41 is offline
    Registered User
    Join Date
    06-30-2010
    Location
    Syracuse, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: get Numbers From Alphanumeric Text

    Thanks for your help, werfnort...

    But maybe it dosen't work or I don't know how to use this function...

    could you give me the instruction how to put and use this function from the beginning to the end...

    I'm a true beginner...

    Thanks...
    Reply With QuoteRegister To Reply
  7. 07-01-2010, 03:24 AM #7
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: get Numbers From Alphanumeric Text

    here's a UDF that I have used
    Please Login or Register  to view this content.
    If the string is in A1 then in B1

    =NumsOnly(A1)
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  8. 07-01-2010, 04:27 AM #8
    mkim41
    mkim41 is offline
    Registered User
    Join Date
    06-30-2010
    Location
    Syracuse, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Smile Re: get Numbers From Alphanumeric Text

    RoyUK...

    Thanks...It works...=)

    However, I have several more questions...

    Fitrst, after I use that function, I got comms just like this...[/SIZE]

    11.28..
    11.28..
    11.28..
    28..
    010207
    28..
    28..
    010207
    06

    Can I remove it?

    Second, I want to put dash between numbers...[/SIZE]

    For example,

    Repair 02 - Tube Nut Thrust Wire InstallationRepair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA

    In this description, I wanna get
    02- 11- 25-

    In this case,

    VRS 3378 - Blend the Fuel Manifolds, Repair 001VRS 3689 - Replace the Anti-Galling Compound on the Fitting, Repair 002NA

    I want to get
    3378- 001- 3689- 002-

    And in this kind of description,

    Repair -19(-23) Replace Rear DetailsRepair -05, -06 Repair Front DetailsRepair -07 Repair Center SupportRepair -19(-23) Weld Seal Bore to Rear SupportNA

    I want to get -10(-23) -05 -06 -07 -19(-23)

    Any advanced formula for get rid of that problem?

    Lastly, I only want to show the number only after Repair, EA, IEN, and VRS...[/SIZE]


    Thanks a lot..!!

    for helping your understanding, this is whole data

    Merge Repair Scheme
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule Details Repair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    Repair 02 - Tube Nut Thrust Wire InstallationRepair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    06 -
    Repair 06 -
    Repair 06 -
    Repair 06 -
    Repair 06 -
    Repair 01 - Apply The Antigalling CompoundRepair 02 - Tube Nut Thrust Wire InstallationRepair 14 - Tube/Duct Assembly And Detail Replacements Repairs (Vendor Application)Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    Repair 14 - Tube/Duct Assembly And Detail Replacements Repairs (Vendor Application)Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsNA
    Repair 21 - Gold Nickel Brazed Repair (Vendor Application)Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsNA
    Repair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 25 - Gold-Nickel Brazed Ext. Tube Assy, Tube Section & Detail Replacement Repair (Vendor Application)NA
    Repair 01 - Apply The Antigalling CompoundRepair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule DetailsRepair 41 - SST Tube Assemblies, Tube & Detail Replacement, Nickel-Braze Repair (Vendor Application)NA
    Repair 14 - Tube/Duct Assembly And Detail Replacements Repairs (Vendor Application)Repair 01 - Apply The Antigalling CompoundRepair 02 - Tube Nut Thrust Wire InstallationRepair 11 - Blend Repair Surface Damage - Tube Connector Nuts, Couplings And Ferrule
    VRS 3759 - Detail Replacement Repair (Replace Heat Shields Only)NA
    Last edited by shg; 07-01-2010 at 10:54 PM.
    Reply With QuoteRegister To Reply
  9. 07-01-2010, 05:08 AM #9
    pike
    pike is offline
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: get Numbers From Alphanumeric Text

    Hi mkim41
    No need to use large font its a pain

    use thids function
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 07-01-2010, 06:36 AM #10
    pike
    pike is offline
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: get Numbers From Alphanumeric Text

    or for all three 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 07-01-2010, 08:53 PM #11
    mkim41
    mkim41 is offline
    Registered User
    Join Date
    06-30-2010
    Location
    Syracuse, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Smile Re: get Numbers From Alphanumeric Text

    Thanks...Pike...

    I have one more question...

    How can I work it automatically whenever I open this excel sheet?

    I really appreciate your help...
    Reply With QuoteRegister To Reply
  12. 07-01-2010, 09:11 PM #12
    pike
    pike is offline
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: get Numbers From Alphanumeric Text

    Hi mkim41,
    What do you mean?
    How do you enter the data?
    The function can be change to a sub to collate a range after opening
    but it all depends on the workbook setup
    Reply With QuoteRegister To Reply
  13. 07-01-2010, 09:20 PM #13
    mkim41
    mkim41 is offline
    Registered User
    Join Date
    06-30-2010
    Location
    Syracuse, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Smile Re: get Numbers From Alphanumeric Text

    I mean if I wanna use this user defined function, do I have to put this formula everytime?
    Reply With QuoteRegister To Reply
  14. 07-01-2010, 09:49 PM #14
    pike
    pike is offline
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: get Numbers From Alphanumeric Text

    you can put it in the first cell and fill down and save.
    how do you enter the dtat?
    Reply With QuoteRegister To Reply
  15. 07-01-2010, 10:02 PM #15
    mkim41
    mkim41 is offline
    Registered User
    Join Date
    06-30-2010
    Location
    Syracuse, NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Smile Re: get Numbers From Alphanumeric Text

    Gonna ask to my supervisor and let you know...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1