I would like to add a character in column, but only if there is 1 digit present. Numbers 5.0

Hi Kokor,

Dr, Frankenstein's process—constructing a whole from its parts—works well here.

Here's a copy of your table showing two solutions, essentially the same.

I've included your initial data in the cells actually involved in the calculations.

Column H uses the first formula, entered in H2, and filled down the rest of column H.

Column J achieves the same results with the second formula, entered in J2, and filled down the rest of column J

H2: "ARIA"&"-"&F2&"-"&LEFT(SUBSTITUTE(B2,".","",occurrence)&"0",2)&D2

J2: CONCATENATE("ARIA","-",F2,"-",LEFT(SUBSTITUTE(B2,".","",occurrence)&"0",2),D2)

As can be seen, the only difference between them is the use of the concatenation operator ( & ) in column H as opposed to the use of the CONCATENATE function in column J.

The only complicated part is this, which takes care of removing the decimal point, if present, and appending a zero, if absent:

LEFT(SUBSTITUTE(B2,".","",occurrence)&"0",2)

SUBSTITUTE replaces any periods in the string of characters retrieved from column B with null strings ( "" )

The concatenation operator ( & ) appends a zero to the results supplied by substitute, formula a two character or three character string (eg. 550 in row 2, 60 in row 3)

LEFT returns the leftmost 2 characters of that string (eg. 55 in row 2, 60 in row 3)

Regards,

Barry

there is a formula in column H. What ever that formula is, let

<EXISTING_FORMULA> represent that formula.

now replace the formula in column H with the following:

=substitute(<EXISTING_FORMULA>, ".", "")

this will substitute nothing for the period

for the existing formula that refers to cell B2. You can do the following:

chang the reference to B2 to 10*B2