Simple macro required to add 001 after a name please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
I would like a Macro to add 001 after a name.

This will now be a one off press of a button then not used.
Some info for you.
Worksheet called POSTAGE
Column B
Range B8:B881

I now need to add "SPACE 001" no quotes to All the names which do not have 002 003 004 etc after them.
My list at present is like

JOHN SMITH
FRED WINTER
FRED WINTER 002
FRED WINTER 003
ETC ETC

So ONLY looking at names like in my example above JOHN SMITH would be changed to JOHN SMITH 001
FRED WINTER would be changed to FRED WINTER 001

So the new list will then start to look like,
JOHN SMITH 001
FRED WINTER 001
FRED WINTER 002
FRED WINTER 003
ETC ETC

This then sorts out my sheet & the new code we have just sorted will take control of starting with the new 001
Otherwise i need to go through my list and manually add the 001 to every names of which is just text.

Names like BOB JONES 002 will not be touched as it has 002 BUT if there is BOB JONES then please add 001 to it

Many Thanks for your time.
Saved me big time.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,646
Office Version
365
Platform
Windows
Try this:
Code:
Sub InsertNums()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
    For Each cell In Range("B8:B881")
        If Len(cell) > 0 And Not IsNumeric(Right(cell, 3)) Then
            cell = cell & " 001"
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,035
Office Version
2010
Platform
Windows
@ipbr21054,

Given that you have a known, fixed range to evaluate, I thought you might find this one-liner macro to be of interest...
Code:
[table="width: 500"]
[tr]
	[td]Sub Add001()
  [B8:B881] = [IF(B8:B881="","",IF(ISNUMBER(-RIGHT(B8:B881)),B8:B881,B8:B881&" 001"))]
End Sub[/td]
[/tr]
[/table]
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,376
Office Version
2007
Platform
Windows
@ipbr21054,

Given that you have a known, fixed range to evaluate, I thought you might find this one-liner macro to be of interest...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Add001()
  [B8:B881] = [IF(B8:B881="","",IF(ISNUMBER(-RIGHT(B8:B881)),B8:B881,B8:B881&" 001"))]
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Thanks but i dont think i will need it anymore.
Reason being i just had toclean up all the names that didnt have a number after them.
Now my new code on another thread will add this.

Can you advise why yours is different or what it would of done.

My range was mentioned as thats as far as i have got down the page.

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,646
Office Version
365
Platform
Windows
Can you advise why yours is different or what it would of done.
It does the same thing, just a different way of doing.
Often times, in Excel (and especially in VBA), there are many different ways to accomplish the same task.
The methodologies are often different, but the results are often exactly the same.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,259
Messages
5,485,716
Members
407,511
Latest member
Tryintouseexcel

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top