Macro question..

[Will17]

Just wanting to know if this is possible. I have a list of plan numbers that I need to add a PS, PS0, PS00, or PS000 depending on how many digits (ex: PS plus 6 digits, ie PS000111). What I usually do is just sort the plan numbers, insert a new column a and c, add PS, PS0, etc depending on what is needed, and concatenate in column c, then paste special values and delete columns a and b. Each week the data is different, different amount of rows, different number of plans that need the PS, PS0, etc.. so can a macro see the difference and automatically add the PS000 to the 111, PS00 for plan number 1111, etc in Column C?

Hopefully I'm making this clear.. I realize it's kinda wordy.

 

[wongm003]

you could use the following code. Highlight the values to updated and run the add_ps macro...

Public Sub add_ps()

For Each c In Selection
    c.Value = "PS" & Format(c.Value, "000000")
Next c

End Sub

You could also use a formula

="PS" & TEXT(A1,"000000")

 

[AlphaFrog]

Or...

• Select the column with the numbers.
• Right-click on the selected column and select Format Cells
  • On the Number tab, select...
  • Catagory: Custom
  • Type:"PS"000000
  • OK

 

[Will17]

Thanks so much. That's a heck of a lot simpler than I thought it would be. Don't know why I never thought of the "PS"00000. Gave it a quick test and it (obviously) worked.