Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

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

Column H is my Frankensteinian way of getting most of the characters I need. I just need to remove the . from any instance in Col C, but add a 0 if there is only one digit.

Currently:

Desired:

iMac with Retina 5K display, macOS High Sierra (10.13.4)

Posted on

Question marked as Solved

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

Posted on

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

Jul 22, 2018 12:59 AM in response to Wayne Contello In response to Wayne Contello

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

chang the reference to B2 to 10*B2

There’s more to the conversation

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

Jul 22, 2018 12:59 AM

Question marked as Solved

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

Apr 20, 2018 6:35 PM