• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 68
  • Last Modified:

Insert a Number to certain fields in EXCEL

I have a spreadsheet "attached" of "Ring Numbers"
The problem I'm having is the data starting with a "R4" and an "R5" need a "1" inserted in between the R and the 4 and also the R and the 5.
For example
"R4142" needs to be "R14142" & "R5507" needs to be "R15507" in order to be joined with another dataset
I am having issues isolating these specific fields only and entering the "1"
  • 2
2 Solutions
NorieVBA ExpertCommented:
Not sure what approach you want, formula or VBA, but here's a formula you could put use.

=IF(OR(LEFT(A2,2)={"R4","R5"}), "R1"&MID(A2,2,1)&MID(A2,3,LEN(A2)),A2)

Insert a column after column A, put that formula in B2, copy down and then do a copy/paste special values.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may manually replace R4 with R14 and R5 with R15 if it'a one time process. Otherwise the similar functionality can be performed with the help of a macro.
GravitaZ24Author Commented:
Subodh! So simple yet effective. Looks like I was way overthinking it... But Norie's is great also, because I might have to do it on additional sheets. Thank you!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome GravitaZ24! Glad you found it useful.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now