insert space

[sna]

Hi Dear,

I can write a macro insert space before Capital letters.
I need to know is there any formula to do this even with helper column.


Thank

[norkaz]

Hi Guy

How many the capital letters in each item, just only 3 letters or it depends?

If it depends, the formula would be complicated , use a helper would be better.

If just only 3 capital letters, try this.

B2

=REPLACE(REPLACE(A2,AGGREGATE(14,6,FIND(CHAR(ROW($65:$90)),A2),1),0," "),AGGREGATE(14,6,FIND(CHAR(ROW($65:$90)),A2),2),0," ")


Norkaz

[sna]

Thanks! one more how can use helper column to do such this issue?

[norkaz]

Try this.

L2
=IFERROR(AGGREGATE(14,6,FIND(CHAR(ROW($A$65:$A$90)),$A2),COLUMN(A1)),1)
Copy to right-hand side and down

D2
=REPLACE(A2,L2,0," ")
Copy down

E2
=TRIM(REPLACE(D2,M2,0," "))
Copy to right-hand side and down

B2
=LOOKUP(CHAR(255),D3:I3)

Norkaz

[sna]

Thank you so much