add number to the middle of an existing number

[thedeadzeds]

Hi Guys,

Is it possible to add a number into the following. It currently looks like CC/748125 but i need it to show CC/00748125 so a double zero after the /

Thanks

 

[MickG]

Perhaps:-

Range("A1").Replace "/", "/00", xlPart

 

[AkaTrouble]

easiest way would be use find/replace ( CTRL + F key click replace tab)

in find box type CC/ in replace box type CC/00

 

[AkaTrouble]

having had some more coffee my offered solution would only work once if used the second time it would replace CC/00123456 with CC/0000123456

depending on the amount of changes needed would have to look at other options

you could apply my method 9 times using find CC/1 CC/2 etc.

or maybe for formula option

=IF((LEFT(A3,5))<>"CC/00",REPLACE(A3,1,3,"CC/00"),A3)

 

[AlKey]

Or this

[B]=SUBSTITUTE(A1,"/","/00")[/B]

 

[SpreedsheetCrusader]

Excel 2010
	E	F
4	CC/748125	CC/00748125
5	ABC/902839	ABC/00902839
6	A/093049	A/00093049
7	283924	283924
8	Acplf/09209303	Acplf/0009209303
Sheet1

Excel 2010
	F
4	CC/00748125
Sheet1

Cell Formulas
Range	Formula
F4	=IF(ISERR(FIND("/",E4)),E4,SUBSTITUTE(E4,"/","/00"))

 

[AkaTrouble]

lots of straight forward solutions Great work Guys (&/or Gals)

i offered my second solution based on lack of examples

some may already be correct numbers

if this is a growing data set and we have replaced the first 100 records and then add 100 new records running the solutions would increase the already correct numbers with an extra 00

i am only offering this reply to highlight this please don't think i am trying to appear to be the smarter one ...

at least 2 of those replying have and do teach me regularly,

it is part of this teaching that has taught me to look for the obvious error as the users of our masterpieces will break them at every opportunity.