Need to trim space between words with fixed length

[shrinivasmj]

NEED TO TRIM SPACE BETWEEN WORDS WITH FIXED LENTH of 10

need formula

EXCEL I HAVE DATA LIKE BELOW IN A1

jhjhd 7778 876 876 out put i need jhjhd7778876 876
fd dsfgd 6546 gfg gg - fddsfgd6546 gfg gg
ee er e r 55 5 t y u uuu - eeerer555t y u uuu

 

[mikerickson]

Re: Need to trim space between words with fixed lenth

This is a case where, in the non-VBA approach, helper columns are better than a monster formula.

 

[Rhodie72]

NEED TO TRIM SPACE BETWEEN WORDS WITH FIXED LENTH of 10

need formula

EXCEL I HAVE DATA LIKE BELOW IN A1

jhjhd 7778 876 876 out put i need jhjhd7778876 876
fd dsfgd 6546 gfg gg - fddsfgd6546 gfg gg
ee er e r 55 5 t y u uuu - eeerer555t y u uuu

There have been some very good answers but this is the best solution. The trick was to recognise there are 2 parts to the formula.

1.) The first 10 characters which may contain unknown quantity of spaces. We count the spaces by removing them and use the formula 10- [length after space removal] for the first part.
2.) 2nd part is working out what needs to be added to the first part. We take the new length and concatenate it (use "&") to join the formulas together. The latter part of the string must be total length less [10+10- [length after space removal]]; thus 20- [length after space removal].
3.) We arrive with the following and trim leading spaces so that there is never a space in the 1st 10 letters... ever.
4.) Viola!

=SUBSTITUTE(LEFT(A1,20-LEN(SUBSTITUTE(LEFT(A1,10)," ","")))," ","") & TRIM(MID(A1,20-LEN(SUBSTITUTE(LEFT(A1,10)," ",""))+1,LEN(A1)-(20-LEN(SUBSTITUTE(LEFT(A1,10)," ","")))))

 

[Rhodie72]

Re: Need to trim space between words with fixed lenth

I found a solution. It's not nice.

	A	B
1	jhjhd 7778 876 876	jhjhd7778876 876
2	fd dsfgd 6546 gfg gg	fddsfgd6546 gfg gg
3	ee er e r 55 5 t y u uuu	eeerer555t y u uuu

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

[Monster formula of unbelievable size!]


WBD

Whew!

What about this:

=SUBSTITUTE(LEFT(A1,20-LEN(SUBSTITUTE(LEFT(A1,10)," ","")))," ","")&TRIM(MID(A1,20-LEN(SUBSTITUTE(LEFT(A1,10)," ",""))+1,LEN(A1)-(20-LEN(SUBSTITUTE(LEFT(A1,10)," ","")))))

Does the job accurately.

 

[Peter_SSs]

Re: Need to trim space between words with fixed lenth

The red text makes sure we quit out if there are no more spaces to replace. Instr returns zero if it doesn't find the text.

WBD

If the target cell has any text at all I think the text I referred to could be omitted. However, if the function is ever pointed at an empty cell then the function would enter an infinite loop, so definitely better left in!!

What about this:

=SUBSTITUTE(LEFT(A1,20-LEN(SUBSTITUTE(LEFT(A1,10)," ","")))," ","")&TRIM(MID(A1,20-LEN(SUBSTITUTE(LEFT(A1,10)," ",""))+1,LEN(A1)-(20-LEN(SUBSTITUTE(LEFT(A1,10)," ","")))))

Does the job accurately.

Possibly.

- If you go to the original post and click 'Reply With Quote' and look at the sample data, you should see that the 3rd sample has two places where there are double spaces (which unfortunately get compacted to one when posted as normal text). If that is so, and the OP has the correct expected outcome, then your formula returns an incorrect result.

- I also suspect that the correct outcome for input of "abcdefghij 123" would "abcdefghij 123". If so, your formula would produce an incorrect outcome for that too.

Perhaps you can modify to achieve the desired outputs if the OP confirms what I have indicated.

 

[Rhodie72]

Re: Need to trim space between words with fixed lenth

Without any suitable data to test it more widely, I have only used the sample data supplied. There are almost always going to be other situations that may arrise but until they arise, we cannot assume they will.

 

[Peter_SSs]

Re: Need to trim space between words with fixed lenth

Without any suitable data to test it more widely, I have only used the sample data supplied. There are almost always going to be other situations that may arrise but until they arise, we cannot assume they will.

My first point was about the sample data supplied, not an "other situation that may arise". It had multiple consecutive spaces in it. I did suggest how you could view that fact.