Remove Brackets containing Numbers with a simple criteria

[heathball]

I cannot find a way to do this> i hope someone can help.

this is an example, and there are lots of numbers that can apply.

The qualifying criteria is the number must end with "L", and there is a "-" placed between the number and the "L" after the removal of the brackets
Other numbers inside brackets, like (3.7) remain as they are.

The qualifiers each sit in one column, in cells with up to 500 other characters, and there are over 800,000 rows.

(16.45L)
needs to become
16.45-L

Jack went to Woolies and almost fainted when learning of the price of a (2.4L) milk carton.
needs to become
Jack went to Woolies and almost fainted when learning of the price of a 2.4-L milk carton.

-no spaces
-maximum of 2 decimals inside the brackets
-maximum number is 100

I imagine a helper column could assist if the task is not efficient on a large data set.

i could create a long vba code with a line for each number, but i suspect there is something possible, and it would be great if i could shorten the task - for future reference as well.

 

[hagia_sofia]

Would this work:

=SUBSTITUTE(A1,TEXTBEFORE(RIGHT(A1,LEN(A1)-SEARCH("(*.*L)",A1)+1)," ",1),SUBSTITUTE(TEXTJOIN("",,TEXTSPLIT(TEXTBEFORE(RIGHT(A1,LEN(A1)-SEARCH("(*.*L)",A1)+1)," ",1),{"(",")"})),"L","-L"))

 

[heathball]

hagia_sofia, that is amazing!
I didn't think it would be so fast.
Thanks very much.

 

[Peter Juhnke]

Hi Heathball, if (16.45l) was in B2 you could use =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"(",""),")",""),"L","-L")
The first substitute finds the left bracket and replaces it with "" (nothing) =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(B2,"(","") ,")",""), "L","-L").
the 2nd substitute looks at the new data without the left bracket finds the right bracket and replaces it with "" (nothing) =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(B2,"(","") ,")",""), "L","-L").
The 3rd substitute looks at the new data without the left and right brackets, finds the "L" and replaces it with "-L" = SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(B2,"(","") ,")",""), "L","-L").
Hope this helps

 

[heathball]

hagia_sofia - there was a piece of information which i did not realise at first.
i see on many rows there is more than one instance of a qualifying number in brackets, and this code desld with the first instance.
But i can seperate this data, now that i have this formula, and get it done that way, so thanks again.

Peter Juhnke - thanks for writing that formula> i am having a look at it.
the 2nd line
i am not having any luck with it at the moment. But i am intrigued by it and thanks for explaining it well. I will keep tinkering with it for now

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(b2,"(","") ,")",""),L,"-L").

 

[Cubist]

Try:

Book1
	A	B
1	Jack went to Woolies and almost fainted when learning of the price of a (2.5L) milk carton.	Jack went to Woolies and almost fainted when learning of the price of a 2.5-L milk carton.
2	This string contains Jack (Josh) Smith. He bout (100.0L) of milk	This string contains Jack (Josh) Smith. He bout 100.0-L of milk
3	Jack (nick name Joe) and Jill (nick name Jay) sold (56.5L) together.	Jack (nick name Joe) and Jill (nick name Jay) sold 56.5-L together.
4	Mya (Mira) Smith drank (56.8L) of juice, while Sam (Samuuel) drank only (2.0L) of juice.	Mya (Mira) Smith drank 56.8-L of juice, while Sam (Samuuel) drank only 2.0-L of juice.
Sheet11

Cell Formulas
Range	Formula
B1:B4	B1	=LET(ts,TEXTSPLIT(A1," "), TEXTJOIN(" ",,IF(ISNUMBER(SEARCH(".",ts))*ISNUMBER(FIND("L",ts)), REPLACE(SUBSTITUTE(SUBSTITUTE(ts,"(",""),")",""),LEN(ts)-2,0,"-"),ts)))

 

[Tom.Jones]

Hi Heathball, if (16.45l) was in B2 you could use =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"(",""),")",""),"L","-L")
The first substitute finds the left bracket and replaces it with "" (nothing) =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(B2,"(","") ,")",""), "L","-L").
the 2nd substitute looks at the new data without the left bracket finds the right bracket and replaces it with "" (nothing) =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(B2,"(","") ,")",""), "L","-L").
The 3rd substitute looks at the new data without the left and right brackets, finds the "L" and replaces it with "-L" = SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(B2,"(","") ,")",""), "L","-L").
Hope this helps

Hi,

Formula is OK but remove any bracket in that string if they are.