Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# remove all other char spaces number from from cell: only the first char after space must remain

Copper Contributor

# remove all other char spaces number from from cell: only the first char after space must remain

hello can anyone advise why this formula i created

=LEFT(TRIM(L161),FIND(" ",(L161))-1) results #value!

send me email email address removed for privacy reasons

7 Replies

# Re: remove all other char spaces number from from cell: only the first char after space must remain

There is no space in L161.

# Re: remove all other char spaces number from from cell: only the first char after space must remain

Can you show some sample?

# Re: remove all other char spaces number from from cell: only the first char after space must remain

here is sample

input

 VARIOUS

expected result must be

 VARIOUS

today, I am getting

 #value!

# Re: remove all other char spaces number from from cell: only the first char after space must remain

this are sample input:
3-1599 SN 0766
VARIOUS
K24.5100.7 S/N 4083/5683/1347

I like the first word only removing others
best response confirmed by QOne7535 (Copper Contributor)
Solution

# Re: remove all other char spaces number from from cell: only the first char after space must remain

If you have Microsoft 365:

=LET(T, TRIM(L161), IFERROR(TEXTBEFORE(T, " "), T))

Otherwise:

=IFERROR(LEFT(TRIM(L161), FIND(" ", TRIM(L161))-1), TRIM(L161))

# Re: remove all other char spaces number from from cell: only the first char after space must remain

``=TAKE(TEXTSPLIT(L161," "),,1)``

# Re: remove all other char spaces number from from cell: only the first char after space must remain

Hans, the first one: this formula Works
=LET(T, TRIM(L161), IFERROR(TEXTBEFORE(T, " "), T))

thank you
1 best response

Accepted Solutions
best response confirmed by QOne7535 (Copper Contributor)
Solution

# Re: remove all other char spaces number from from cell: only the first char after space must remain

If you have Microsoft 365:

=LET(T, TRIM(L161), IFERROR(TEXTBEFORE(T, " "), T))

Otherwise:

=IFERROR(LEFT(TRIM(L161), FIND(" ", TRIM(L161))-1), TRIM(L161))