• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inserting a letter in a cell

B

brightyoyo

New Member
Hi, I would like to add a letter infront of several numbers in a cell.

I would like to take this: C12,16,19,26,27,41,50,74,75 and turn it into

C12,C16,C19,C26,C27,C41,C50,C74,C75

I have hundreds to do like that, so something automated would make it a lot easier.


Thank You
 
Brightyoyo


Firstly, Welcome to the Chandoo.org Forums


You can select the cells and apply a custom format of


Is there any logic as to which cells this applies to?
 
If all of those numbers are in different cells:

="C"&A2

IF all of those numbers are in a single cell:

=SUBSTITUTE(A2,",",",C")
 
Hi ,


To make Luke's formula a little more robust !


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("C"&A2," ",""),",",",C"),"CC","C")


Narayan
 
Yikes Narayan! Nicely done. Took me a little bit to keep all those commas and quotation marks, but I like it. =)
 
If they are all numbers in a single cell, another easy way is to do a Find & Replace (Ctrl+H) replacing "," with ",C"
 
Thank you for such a quick response. The formula =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("C"&A2," ",""),",",",C"),"CC","C")works great. My only the issue is that I have more numbers that do not have a C, but R, CR, Q, and E (they are reference designators for circuit boards) that have include. Is there anyway to have one formula to do this depending on the first letter in the sequence.

C6,29,20,79,80

CR25,26,27,28,29,30

Q1,2,3,4

R4,7,13,14,68,89

E1,2,3,4,5
 
I believe all you'll have to do is just change the letter from "C" to the other one...like such


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("CR"&A2," ",""),",",",CR"),"CRCR","CR")

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("Q"&A2," ",""),",",",Q"),"QQ","Q")

etc
 
I see how changing the letter would work, but I was wondering if it could be one formula that would work no matter what the letter is by identifying the first letter in the string of numbers.
 
Hi ,


Instead of the fixed character "C" , use =LEFT(A2,MIN(IFERROR(SEARCH({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"},A2),999))-1)


or


=LEFT(A2,MIN(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A2),999))-1)


to get the first few alpha characters in the cell. Then use these instead of the fixed "C".


Narayan
 
Hi Narayank991 That did not work for me, I started with C12,16,19,26,27,41,50,74,75,83,84,85,24

and after the formula I got

C12,16,19,26,27,41,5
 
try this one:

=SUBSTITUTE(A2,",",","&LEFT(A2,MIN(SEARCH({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"},A2))-1))
 
Hi Luke M, the formula is giving me intermitting success

here is one result

C7,C7,10,C7,11,C7,13,C7,14,C7,15,C7,17,C7,21,C7,22,C7,23,C7,25,C7,31,C7,37,C7,38,C7,46,C7,47,C7,51,C7,52,C7,53,C7,54,C7,55,C7,56,C7,57,C7,58,C7,59,C7,60,C7,61,C7,62,C7,63,C7,64,C7,65,C7,66,C7,67,C7,68,C7,69,C7,70,C7,71,C7,72,C7,73,C7,87,C7,88,C7,89,C7,90

It pick C7 evertime, but did not put a C in front of the rest. Must of the time I am getting a #value error, especially when there is only one or two values in the cell. Each cell could have between 1 and 50 values.
 
Hmm. Mind just doing a quick copy/paste of the different samples of data you have? This would let us know what all cases to consider.
 
Hello,

Similar idea to the formulas posted above:

= SUBSTITUTE(H2,",","," & LEFT(H2,MATCH(TRUE, ISNUMBER(MID(H2,ROW($A$1:INDEX($A:$A,LEN(H2))),1)+0), 0)-1))

enter with Ctrl + Shift + Enter


Where H2 refers to the cell that contains the text that needs to be processed


I got the following output:

[pre]
Code: 
Orig	             Result
C6,29,20,79,80	     C6,C29,C20,C79,C80
CR25,26,27,28,29,30  CR25,CR26,CR27,CR28,CR29,CR30
Q1,2,3,4	     Q1,Q2,Q3,Q4
R4,7,13,14,68,89     R4,R7,R13,R14,R68,R89
E1,2,3,4,5	     E1,E2,E3,E4,E5
[/pre]
Cheers,

Sajan.
 
Hi Luke,

I think a slight tweak is needed to your formula, since MIN does not handle error values


=SUBSTITUTE(H2,",",","&LEFT(H2,MIN(IFERROR(SEARCH({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"},H2), 999))-1))

entered with Ctrl + Shift + Enter


where H2 refers to the cell text that needs to be processed.


Cheers,

Sajan.


P.S. Narayan's formula included the IFERROR(...)
 
Hi Luke M, Here is an example of my data

C1,2,3,4,28,30

C12,16,19,26,27,41,50,74,75,83,84,85,24

C18,32

C33,42

C35,44

C36,45

C39,40,48,49,81,82

C5

C6,29,20,79,80

C7,10,11,13,14,15,17,21,22,23,25,31,37,38,46,47,51,52,53,54,55,56,57,58,59,60,61,62,63

CR5,12,14,15,18,19

CR7,16,20,21,22

CR8

J1

L1,L2

L3,4

Q1,2,3,4

Q11

Q13

Q5,6,7,10,12

Q8

Q9

R1,27,28,35,36

R10,46

R12

R15,19

R2

R20

R22
 
Hi ,


This seems to work :


=SUBSTITUTE(LEFT(A2,MIN(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A2),999))-1)&SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A2),999))-1),""),",",","&LEFT(A2,MIN(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A2),999))-1))


Enter it as an array formula , using CTRL SHIFT ENTER.


Narayan


P.S. Hui , your formula will not work with L1,L2 , or where the string starts with two alpha characters which need to be repeated , as in CR5.
 
You must log in or register to reply here.
Back
Top