Page 1 of 1
insert space
Posted: Tue Apr 06, 2021 6:22 pm
by 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
Re: insert space
Posted: Tue Apr 06, 2021 8:29 pm
by 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
Re: insert space
Posted: Tue Apr 06, 2021 9:25 pm
by sna
Thanks! one more how can use helper column to do such this issue?
Re: insert space
Posted: Tue Apr 06, 2021 9:29 pm
by 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
Re: insert space
Posted: Tue Apr 06, 2021 10:27 pm
by sna
Thank you so much