How to replace 1 or more periods at end of value

[DashboardNovice]

In the attached file, I have some values in column G which has 1 or more periods at the end of the value. I am trying to find a way to delete any periods (no matter how many there are) at the end of each value.

If a period appears in the middle of the value, it should be kept.

Column I has what the final result should look like.

I am trying to come up with a formula that can do this.

 

[Peter Bartholomew]

This relies upon there being no spaces. It turns all the periods into spaces and then trims the excess spaces. Finally it replaces the in-string periods.

= SUBSTITUTE( TRIM( SUBSTITUTE( fromString, ".", " ") ), " ", "." )

 

[bosco_yip]

Or this one, can allow spacing inside the string as per above picture

In I5, enter formula:

=LEFT(G5,AGGREGATE(14,6,ROW($1:$99)/(MID(G5,ROW($1:$99),1)>="0"),1))

or,

=LEFT(G5,MATCH(2,1/(MID(G5,MMULT(ROW($1:$99),1),1)>="0")))

and copied down

Regards
Bosco

 

[Peter Bartholomew]

… but soon it will look like this!



=LEFT(G5,MAX((MID(G5,SEQUENCE(99),1)>="0" )*SEQUENCE(99)))

 

[AliGW]

... but for anyone without the insider knowledge, it will return #NAME?

 

[Peter Bartholomew]

Ali Afraid so.
You would be missing the SEQUENCE function.
There are ways round it but that simply takes one back in the direction of Bosco's solution.

 

[AliGW]

I'm an insider - it looks really promising. Some good new functions coming up - biggest changes for quite some time, really.

 

[DashboardNovice]

This relies upon there being no spaces. It turns all the periods into spaces and then trims the excess spaces. Finally it replaces the in-string periods.

= SUBSTITUTE( TRIM( SUBSTITUTE( fromString, ".", " ") ), " ", "." )

This worked. I thought it would work only for 2 periods but it works for more. How does this work for more than 2 periods at the end, since you have the SUBSTITUTE function only twice?

 

[Peter Bartholomew]

Hi
The SUBSTITUTE is set to switch all occurrences each time it is used (no specific instance is set). The first application turns periods→spaces and, once the spaces are trimmed, the second application turns all remaining spaces→periods.

 

[Ron Coderre]

A variation that avoids MMULT (a powerful function, but one I can never clearly explain to others)

=LEFT(G5,MATCH(2,INDEX(1/(MID(G5,ROW($1:$99),1)>="0"),0)))

 

[AliGW]

This worked. I thought it would work only for 2 periods but it works for more. How does this work for more than 2 periods at the end, since you have the SUBSTITUTE function only twice?

Because the first (inner) SUBSTITUTE replaces each full stop (period) with a space. This is then trimmed using the TRIM function, so ALL trailing spaces are removed. Then the remaining spaces are replaced with a full stop (period) by the second (outer) SUBSTITUTE.

 

[GraH - Guido]

Another way using Power Query, allowing spaces as in Bosco's solution.

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Replace"".""byLineFeed" = Table.ReplaceValue(Source,".","#(cr) ",Replacer.ReplaceText,{"From"}),
  TrimIt = Table.TransformColumns(#"Replace"".""byLineFeed",{{"From", Text.Trim, type text}}),
  #"ReplaceLineFeedBy"".""" = Table.ReplaceValue(TrimIt,"#(cr)",".",Replacer.ReplaceText,{"From"}),
  RenameFrom_To = Table.RenameColumns(#"ReplaceLineFeedBy"".""",{{"From", "To"}})
in
  RenameFrom_To