+ Reply to Thread
Results 1 to 5 of 5

Splitting numbers and custom format text values using VBA

  1. #1
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    325

    Splitting numbers and custom format text values using VBA

    Hello,

    I tried the below code for splitting numbers and custom format text values. However I want to replace the custom text values with the standard names like

    gms for GMS
    Kilograms for KGS
    ton for TON
    numbers for NOS

    2.I made a mistake with the clearcontents part as the code is clearing the headers also

    Please Login or Register  to view this content.
    Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,894

    Re: Splitting numbers and custom format text values using VBA

    deleted....
    Last edited by protonLeah; 09-29-2021 at 11:17 PM.
    Ben Van Johnson

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,763

    Cool Hello ! Try this !


    According to your attachment your VBA procedures revamped :

    PHP Code: 
    Sub Clearall()
        [
    H1].CurrentRegion.Offset(1).ClearContents
    End Sub

    Sub Splitmou
    ()
        
    Dim VWR&, XY
            V 
    = [A1].CurrentRegion.Resize(, 3).Value2:  V(13) = "UNIT"
            
    = [M5].CurrentRegion.Columns(2).Value2
            Clearall
        
    For 2 To UBound(V)
                
    Split(Cells(R2).Text)
            If 
    UBound(X) = 1 Then
                Y 
    Application.Match(Left(X(1), 1) & "*"W0)
                If 
    IsError(YThen V(R3) = X(1) Else V(R3) = W(Y1)
            Else
                
    V(R3) = "?"
            
    End If
        
    Next
            
    [H1:J1].Resize(1).Value2 V
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 09-29-2021 at 08:30 PM.

  4. #4
    Forum Contributor
    Join Date
    06-20-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    325

    Re: Hello ! Try this !

    Thank you for the revamped VBA. It takes time for me to understand this high level code.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,894

    Re: Splitting numbers and custom format text values using VBA

    deleted.....
    Last edited by protonLeah; 09-29-2021 at 11:18 PM.

+ 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: 10
    Last Post: 11-25-2019, 07:12 PM
  2. [SOLVED] trimming space between splitting numbers and text
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-04-2017, 01:17 PM
  3. [SOLVED] Splitting Text and Numbers from the one cell
    By coach.32 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-28-2015, 04:06 PM
  4. custom format with text and numbers
    By source in forum Excel General
    Replies: 5
    Last Post: 10-31-2014, 02:54 PM
  5. Replies: 19
    Last Post: 07-20-2014, 04:55 PM
  6. [SOLVED] Merging Numbers with custom format - how to keep format
    By oddcarout in forum Excel General
    Replies: 3
    Last Post: 08-15-2012, 07:54 PM
  7. How to format text and numbers as custom
    By Julian Ganoudis in forum Excel General
    Replies: 4
    Last Post: 04-04-2005, 02:06 PM

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