+ Reply to Thread
Results 1 to 11 of 11

Replace the first number in three groups only

  1. #1
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Replace the first number in three groups only

    Hi experts
    There is a string "12546-10203-32198-98876-100047"

    These are 5 groups, I wanted to replace the first number of the three groups in the middle only with conditions
    0 = A
    1=D
    2=Z
    3=K
    4=M
    5=X
    6=C
    7=S
    8=Q
    9=F

    so the result of the example string should have be
    12546-D0203-K2198-F8876-100047

  2. #2
    Forum Expert Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,430

    Re: Replace the first number in three groups only

    Assumes strings in Column A
    Please Login or Register  to view this content.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  3. #3
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Replace the first number in three groups only

    Thanks a lot for this big help

  4. #4
    Valued Forum Contributor
    Join Date
    06-27-2010
    Location
    sYRIA
    MS-Off Ver
    Excel 2013
    Posts
    669

    Re: Replace the first number in three groups only

    @sintek
    HI
    It is a very nice piece of code
    But I Believe that splitting one thing 4 times is time consumption
    so I suggest this amendment to you code
    Do you agree ?
    No offence though
    Please Login or Register  to view this content.

  5. #5
    Forum Expert Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,430

    Re: Replace the first number in three groups only

    Apologies...Found an error in my code...This fixes...

    @ mohadin
    ...None taken...
    My code had errors though so perhaps re-look at your simplification offered...

    Please Login or Register  to view this content.

    @ KingTamo
    Thanks for rep +
    Last edited by Sintek; 09-17-2022 at 07:49 AM.

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

    Re: Replace the first number in three groups only

    Quote Originally Posted by KingTamo View Post
    Hi experts
    There is a string "12546-10203-32198-98876-100047"

    so the result of the example string should have be
    12546-D0203-K2198-F8876-100047
    only when the first charcter is numeric.
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Replace the first number in three groups only

    Thanks a lot everybody
    You are great people

  8. #8
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Replace the first number in three groups only

    @jindon
    Your code adds the letter and didn't remove the number (The number should be replaced)
    12546-D0203-K2198-F8876-100047

  9. #9
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,401

    Re: Replace the first number in three groups only

    Here one more variant

    Please Login or Register  to view this content.
    Last edited by JEC.; 09-17-2022 at 12:37 PM.

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

    Re: Replace the first number in three groups only

    Missex q...
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    11-24-2016
    Location
    Libya
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Replace the first number in three groups only

    @jindon

    thanks a lot for great and helpful help
    cheers
    Last edited by KingTamo; 09-17-2022 at 01:58 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. [SOLVED] Same Cust Number within 2 or more groups
    By MarkJohn51 in forum Excel General
    Replies: 12
    Last Post: 06-03-2020, 06:21 PM
  2. Replies: 4
    Last Post: 12-03-2019, 12:59 AM
  3. Replies: 11
    Last Post: 11-19-2019, 11:36 PM
  4. Counting Number of groups
    By Dezenzerrick in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-04-2016, 12:11 PM
  5. Formatting Number Groups
    By taffski51 in forum Excel General
    Replies: 4
    Last Post: 07-08-2012, 06:17 PM
  6. code to break a number down into groups
    By Traymond in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2009, 10:57 PM
  7. 2 questions - DB and number groups
    By Rodney in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-08-2005, 08:06 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