Removing numeric Percentages from middle of string

[moexius]

Hi there,

I have an issue with some text formatting in a formula

My client wants to display the following: "3 Kleuren HQ buiten 75 + glanslak 75" (no quotes ofc)

the values I get from the table are

ART_ChildSHTPD_Desc1

1 Color 10%
1 Color 20%
1 Color 30%
1 Color 40%
1 Color HQ buiten 100% + glanslak 100%
1 Color HQ buiten 40% + glanslak 40%
1 Color HQ buiten 75% + glanslak 75%
2 Colors 20%
2 Colors 30%
2 Colors 40%
2 Colors 50%
2 Colors 60%
2 Colors 80%
2 Colors HQ buiten 100% + glanslak 100%
2 Colors HQ buiten 75% + glanslak 75%
3 Colors 100%
3 Colors HQ buiten 75% + glanslak 75%
4 Colors 60%
4 Colors 80%
4 Colors HQ buiten 40% + glanslak 40%
4 Colors HQ buiten 75% + glanslak 75%
5 Colors HQ buiten 75% + glanslak 75%
Code and/or Resy
Code en/of Resy
Litho

I need to remove the percentage and the numbers before it.

ART_ChildSHTPD_Desc1

1 Color
1 Color HQ buiten + glanslak
2 Colors
2 Colors HQ buiten + glanslak
3 Colors
3 Colors HQ buiten + glanslak
4 Colors
4 Colors HQ buiten + glanslak
5 Colors
5 Colors HQ buiten + glanslak
Code and/or Resy
Code en/of Resy
Litho

So far i am able to replace the '$' with nothing. I dont know how to remove the number left of '%'.

How can I get this done?

PS: if the above is too troublesome to achieve, i could also do with a formula to remove all numeric values from that string:

ART_ChildSHTPD_Desc1

Color
Color HQ buiten + glanslak
Colors
Colors HQ buiten + glanslak
Code and/or Resy
Code en/of Resy
Litho

 

[CrystalizeCanada]

One formula that might work depending on the variety of data you have:

StringVar DataToFix := "1 Color HQ buiten 100% + glanslak 100%"; //Replace with your string field
DataToFix := Replace(DataToFix, "%", "");//Get rid of %
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "1", "", 4);//Get rid of 1
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "2", "", 4);//Get rid of 2
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "3", "", 4);//Get rid of 3
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "4", "", 4);//Get rid of 4
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "5", "", 4);//Get rid of 5
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "6", "", 4);//Get rid of 6
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "7", "", 4);//Get rid of 7
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "8", "", 4);//Get rid of 8
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "9", "", 4);//Get rid of 9
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, "0", "", 4);//Get rid of 0
DataToFix := DataToFix [1 to 3] & Replace(DataToFix, " ", " ", 4);//Get rid of two spaces and replace with one

Gordon BOCP
Crystalize

 

[CrystalizeCanada]

Another way of doing it is to loop through each letter and only keep the non-numeric ones.

StringVar DataToFix := "1 Color HQ buiten 100% + glanslak 100%"; //Replace with your string field
NumberVar i;
stringVar FixedData;
//Loop through each letter
For i:= 3 to len(DataToFix) do
if Not NumericText(DataToFix) then
FixedData := FixedData & DataToFix;
//Get rid of the % and then extra space
FixedData := Replace(FixedData, "%","");
FixedData := Replace(FixedData, Space(2), Space(1));//Two spaces replace with one space
//FORMULA RESULT:
DataToFix [1 to 2] & FixedData

Gordon BOCP
Crystalize

 

[pmax9999]

A relatively simple approach would be to use nested Replace functions.

The following should work:

Left({Table.ART_ChildSHTPD_Desc1},5) + Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(MID({Table.ART_ChildSHTPD_Desc1},6), '9', ''), '8', ''), '7', ''), '6', ''), '5', ''), '4', ''), '3', ''), '2', ''), '1', ''),'0', ''), '%', '')

It assumes the numeric characters to be excluded will not appear in the first 5 characters.

Hope it helps.

Cheers
Pete