Remove duplicates from a string only from right

[maamon]

Hi, I have a problem and surprisingly I cannot find easy answer to this. Is there a way to use formula (even complexed one) to remove repeating signs or letters ONLY from the end of a string in excel? For example to remove all "\" from the end of strings below (each would be in a separate row), but leaving "\" in the rest of the string:

\\emea.com\folder_01\subfolder\many_files\\\
\\emea.com\folder_02\subfolder2\something\\\\\
\\emea.com\folder_03\sub\\

There will be hundreds of rows so I need one formula to drag down...

Thanks!

[hrlngrv]

Given your example strings above, do you want the final results to be

\\emea.com\folder_01\subfolder\many_files
\\emea.com\folder_02\subfolder2\something
\\emea.com\folder_03\sub

? If so, easier to use a defined name like seq referring to =ROW(INDEX($1:$1048576,1,1):INDEX($1:$1048576,255,1))-1 and cell formulas like

Please Login or Register  to view this content.

where X99 is a placeholder for the actual cells addresses containing such strings. ADDED: this is an array formula; hold down [Ctrl] and [Shift] keys before pressing [Enter].

The definition of seq is immune to row insertion/deletion. It evaluates to an array of sequential integers from 0 to 254. If your strings could be longer than 255 characters, increase the 255 in the definition of seq.

This is something both LibreOffice Calc and Google Sheets handle much better than Excel. Those others provide regular expression functions. In Google Sheets, this only takes

=REGEXREPLACE(X99,"\\*$","")

[Rick Rothstein]

This array-entered** formula should also work...

Please Login or Register  to view this content.

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

[Adtxc]

If your text always begin with double backslash then you can use this formula

=LEFT(A1,LEN(TRIM(SUBSTITUTE(A1,"\"," ")))+2)

[Rick Rothstein]

If your text always begin with double backslash then you can use this formula

=LEFT(A1,LEN(TRIM(SUBSTITUTE(A1,"\"," ")))+2)

What about if a directory name begins or ends with a space or has two or more consecutive spaces in its name? Not likely but definitely possible.