Split Cell by Character Length excluding Spaces

[mik1996]

Hi, I'm looking for a formula or some coding that can split characters in excel excluding spaces, i work for a bank and the character limit is 30 for the beneficiary name (excluding spaces) so i need to cut and move anything after 30 characters excluding spaces in to the next column 'Beneficiary Name 2? ive tried a number of different ways but all of them include the spaces as characters, Is anyone able to help with this please?

Benficiary Name	Benficiary Name 2
Client SSAI Account Nuclear Banks Services and Solutions ALLLFSM

 

[James006]

Hi,

Have you tried to use from the main menu Text to Columns ...?

How to Use Text to Columns Like an Excel Pro

Text to Columns in Excel is great. But are you getting the most out of it? Learn to use Text to Column like a pro.

www.howtogeek.com

 

[Anthony47]

For example, in B2

=LEFT(A2,MAX(IF(LEN(SUBSTITUTE(LEFT(A2,{30,31,32,33,34,35,36,37,38,39})," ",""))<31,{30,31,32,33,34,35,36,37,38,39},"")))

This deals with a max of 10 spaces; if you feel that is not enough then extend that 2 arrays of constants adding more options

PS not sure that {30,31,32,33,34,35,36,37,38,39} is the correct syntax for an array of constants

MULTI_C30319.xlsm
	A	B	C
1	Benficiary Name	Benficiary Name2
2	Client SSAI Account Nuclear Banks Services and Solutions ALLLFSM	Client SSAI Account Nuclear Banks S
3
4
Sheet2

Cell Formulas
Range	Formula
B2	B2	=LEFT(A2,MAX(IF(LEN(SUBSTITUTE(LEFT(A2,{30,31,32,33,34,35,36,37,38,39})," ",""))<31,{30,31,32,33,34,35,36,37,38,39},"")))

 

[mik1996]

Hi,

Have you tried to use from the main menu Text to Columns ...?

How to Use Text to Columns Like an Excel Pro

Text to Columns in Excel is great. But are you getting the most out of it? Learn to use Text to Column like a pro.

www.howtogeek.com

thanks, yes i have tried this but again when i use text2columns it includes the spaces as characters so when i select 30 its including the spaces aswell if that makes sense

 

[James006]

But ...
Have you used the choice DELIMITED ... followed by the choice SPACES ... before indicating the destination ...?

 

[mik1996]

=LEFT(A2,MAX(IF(LEN(SUBSTITUTE(LEFT(A2,{30,31,32,33,34,35,36,37,38,39})," ",""))<31,{30,31,32,33,34,35,36,37,38,39},"")))

That works great actually thanks so much, sorry just realised i would also like split anything after the 30 characters (excl spaces) to be moved into another column is there another code i can use to do that?

 

[Joe4]

But ...
Have you used the choice DELIMITED ... followed by the choice SPACES ... before indicating the destination ...?

Go back and read the question again. I think you may have misunderstood it.
They are NOT trying to split after every space. I believe they want to split after 30 characters, but not in the middle of a word (so it may be less than 30 characters, depending on the value).

 

[Anthony47]

i would also like split anything after the 30 characters (excl spaces) to be moved into another column is there another code i can use to do that?

In C2:

=SUBSTITUTE(A2,B2,"")

MULTI_C30319.xlsm
	A	B	C
1	Benficiary Name	Benficiary Name2	Next
2	Client SSAI Account Nuclear Banks Services and Solutions ALLLFSM	Client SSAI Account Nuclear Banks S	ervices and Solutions ALLLFSM
3
Sheet2

Cell Formulas
Range	Formula
B2	B2	=LEFT(A2,MAX(IF(LEN(SUBSTITUTE(LEFT(A2,{30,31,32,33,34,35,36,37,38,39})," ",""))<31,{30,31,32,33,34,35,36,37,38,39},"")))
C2	C2	=SUBSTITUTE(A2,B2,"")

 

[mik1996]

In C2:

=SUBSTITUTE(A2,B2,"")

MULTI_C30319.xlsm
	A	B	C
1	Benficiary Name	Benficiary Name2	Next
2	Client SSAI Account Nuclear Banks Services and Solutions ALLLFSM	Client SSAI Account Nuclear Banks S	ervices and Solutions ALLLFSM
3
Sheet2

Cell Formulas
Range	Formula
B2	B2	=LEFT(A2,MAX(IF(LEN(SUBSTITUTE(LEFT(A2,{30,31,32,33,34,35,36,37,38,39})," ",""))<31,{30,31,32,33,34,35,36,37,38,39},"")))
C2	C2	=SUBSTITUTE(A2,B2,"")

thats exactly what i needed, thank you for all your help much appreciated!

 

[Anthony47]

Thank you for the feedback