CIO Tech Eksperten IT-JOB IT-Kurser Events Podcast Søg

Log ind eller opret profil

Du kan også logge ind via nedenstående tjenester

Nuser2008 Mester

06. december 2024 - 13:35 Der er 8 kommentarer og
2 løsninger

Remove blank space in cell

Hi Forum,

I hope you can help me with a formula so I can remove blank space after the last letter in cell.

Examples:
A) World [5 blanks] Nordic [5 blanks] Denmark [175 blanks]
B) World [5 blanks] Denmark [160 blanks]

In example A, it's the 175 blanks that I would like to get removed.
In example B, it's the 160 blanks that I would like to get removed.

The 5 blanks between World, Nordic and Denmark may not be removed, only the blanks after the last letter.

I've tried several combinations of Substitute and Trim but without the needed result.

Thanks in advance.

Kind regards,
Arne

Synes godt om

jens48 Ekspert

06. december 2024 - 14:13 #1

This formula finds the last letter in the string and erases all following spaces:

=LEFT(A$1;MAX(IFERROR(SEARCH({"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"u";"v";"w";"x";"y";"z";"æ";"ø";"å"};A$1;1);"")))

Synes godt om

jens48 Ekspert

06. december 2024 - 14:16 #2

Sorry, I forgot the "t"

=LEFT(B$1;MAX(IFERROR(SEARCH({"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z";"æ";"ø";"å"};B$1;1);"")))

Synes godt om

Nuser2008 Mester

06. december 2024 - 14:41 #3

Hi Jens!

Thanks for your suggestion.

Somehow, too much text is being removed:

Before removal of blanks: FI400029-Norway-Fejlkode B [+ many blanks]
After removal of blanks: FI400029-Norway-Fejl [no blanks]

The longer the string, the more letters are being removed.

So the issue with the blanks has been solved but it works way too good! Do you have a solution on this or can see how we can optimize your formula?

/Arne

Synes godt om

jens48 Ekspert

06. december 2024 - 14:46 #4

I have noticed that it does not work if the last letter appears more than once. Try this one instead:

=LEFT(B$1;LEN(TEXTBEFORE(B1;{"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z";"æ";"ø";"å"};-1))+1)

Synes godt om

ebea Ekspert

06. december 2024 - 14:52 #5

Another way to do it: =IF(RIGHT(A1;1)=" ";LEFT(A1;LEN(A1)-1);A1)

Synes godt om

1 synes godt om dette

Nuser2008 Mester

06. december 2024 - 16:14 #6

@jens48: Thanks. It works, but the very last letter in the sentence is missing/has been cut off. I've tried to adjust the number values (-1 and +1) in the formula but it doesn't change anything. I'll add a right formula to your formula so I keep the last letter. If you have time for an updated formula, it would be great but else I keep it as is.

@ebea: Thanks for your suggestion. However, the number of blanks do not change after implementing your formula. I'm not that strong in "reading" formulas so I can't see what might be wrong.

Synes godt om

ebea Ekspert

06. december 2024 - 16:32 #7

#6 - can we agree, that it's the blank after the last word you want to remove ?

And in my test, my suggested formula works for that purpose.

So why not write the text as it is, and give a better explanation, instead of writing the way you do (with all [Blanks].

PS: Your name seems to be danish, so are there a specific reason to write in English ?

Synes godt om

jens48 Ekspert

06. december 2024 - 18:10 #8

I have checked it again here, and it works OK. But don't you mean "the last number" instead of "the last letter"? If the last character is a number you will have to modify the formula to:

=LEFT(B$1;LEN(TEXTBEFORE(B$1;{"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z";"æ";"ø";"å";"0";"1";"2";"4";"5";"6";"7";"8";"9"};-1))+1)

Synes godt om

1 synes godt om dette

kim1a Ekspert

08. december 2024 - 09:44 #9

Excel has a trim function.
But the blank might not be an actual blank, it might be a wierd character, så try copying it and search replace with blank if it is not seen as a blank.

Synes godt om

1 synes godt om dette

Dan Elgaard Ekspert

08. december 2024 - 12:31 #10

This is the universal formula:

=LET(S;A1;F;CHAR(1);T;SUBSTITUTE(CLEAN(S);CHAR(160);" ");U;SUBSTITUTE(T;" ";"");N;FIND(LEFT(U;1);T);C;RIGHT(U;1);L;MID(T;N;LEN(T));R;LEFT(T;FIND(F;SUBSTITUTE(T;C;F;LEN(T)-LEN(SUBSTITUTE(T;C;"")))));B;MID(R;N;LEN(T));R)

Notive the 'R' at the end of the formula.
That is what's removing all spaces to the Right.
Change it to 'L' if you want to remove all spaces to the Left.
Change it to 'B' if you want to remove from Both left and right.

Change 'A1' to your own cell.
Spaces inbetween the text is not affected.

Synes godt om

1 synes godt om dette

Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Følg dette spørgsmål

Opret Preview

Excel kurser

Vi har et stort udvalg af Excel kurser. Find lige det kursus der passer dig lige her.

Se alle Excel kurser

Flere spørgsmål fra Excel kategorien

Titel	Indlæg	Oprettet	Seneste aktivitet
To x-akser men kun ét datasæt? Af TheLibrarian i Excel	1	I går 09:17	I går 10:35
Isolere en dato i tekst/tal Af jernrosen3 i Excel	7	09/12/202408:59	09/12/202410:38
Udtag numerisk værdi af blandet tekst og tal? Af FiSc i Excel	2	08/12/202411:35	08/12/202412:53
Makro - Søg efter værdi på tværs af flere ark Af Chjes94 i Excel	5	02/12/202414:31	03/12/202413:43
PDF viewer i VBA kode Af ingeman i Excel	5	01/12/202417:36	03/12/202416:33

Se alle spørgsmål i kategorien Opret spørgsmål

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester

Alle kategorier på Eksperten

Seneste artiklerRSS