How to know the end of string, remove extra space in string

[aamirsq]

Hi everyone,

Hope you are doing fine, i have a question using the mid () function is it possible to find (to go at) of a string ?

e.g. In a cell i have

1. abc001 [extra space]a quick brown jumps over the lazy dog.[extra spaces here i dont know how many btw i tried trim()]

2. abc002 A quick quick fox jumps over the lazy dog. [extra space only 1]

3. abc003-a quick brown fox [extra space here] jumps over[extra space here] the laxy dog.[no extra space]

thanks

 

[Somendra Misra]

Hi,

Try below formula first to set a string.

=TRIM(SUBSTITUTE(A1," "," "))

Then use appropriate formula around it.

Regards,

 

[prasaddn]

Hi, aamirsq,

I agree with SM's solution, but one catch if the 2nd argument in substitute formula is with one space, then it will replace all the spaces in the given string.

can you explain what is your expected outcome with the same example given above.

Regards,
Prasad DN

 

[Mike H..]

Hi everyone,

Hope you are doing fine, i have a question using the mid () function is it possible to find (to go at) of a string ?

e.g. In a cell i have

1. abc001 [extra space]a quick brown jumps over the lazy dog.[extra spaces here i dont know how many btw i tried trim()]

2. abc002 A quick quick fox jumps over the lazy dog. [extra space only 1]

3. abc003-a quick brown fox [extra space here] jumps over[extra space here] the laxy dog.[no extra space]

thanks

Hi,

=TRIM(A1)

Will remove all leading, trailing spaces and in addition convert the multiple spaces between words to a single spaces. If that's not working for you then perhaps they're not spaces. Often with imported data you can get something called character 160 between the words and that character looks like a normal space. If TRIM doesn't work then try this:-

=TRIM(SUBSTITUTE(A1,CHAR(160),""))

 

[aamirsq]

thanks ..