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

Question:

Question: 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:

User uploaded file


Desired:

User uploaded file


Thank you in advance.

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

Posted on

Reply
Question marked as Solved
Answer:
Answer:

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.

User uploaded file

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

Question marked as Helpful

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

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

Question marked as Helpful

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

Read all replies

Page content loaded

Question marked as Helpful

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

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

Reply Helpful (1)
Question marked as Solved

Apr 20, 2018 6:35 PM in response to Kokor1 In response to Kokor1

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.

User uploaded file

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

Reply Helpful
Question marked as Helpful

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

Jul 22, 2018 12:59 AM

Reply Helpful (1)
User profile for user: Kokor1

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