LEFT formula without spaces

[charliew]

Morning everyone,

Firstly i'm sorry if this question or one similar has already been posted, i did have a good rummage but couldn't find anything that worked. I would also like to clarify that i didn't build this spreadsheet!!

I have this data in B3874

West Herts GC, Croxley Green, WD3 3GG

This is inputted using formula : =@Stock!C:C

I am currently using formula : =UPPER(LEFT(B3874,6))

Which brings results : WEST H (obviously)

This is incorrect for my needs. I need the first 6 characters NOT including spaces - WESTHE

Now, the space will be in a different location depending on the customer name, which makes it a bit harder...

Another challenge i have is if the company has less than 6 characters the additional characters need to be Q to make up the additional characters this could be identified as before the comma?

SAS, Skipton, BD23 2TZ

the above should be SASQQQ


If anyone can help, I'd be very much grateful!!!

Thank you!

 

[FormR]

Hi, you could give this a try.

Book2
	A	B
1	West Herts GC, Croxley Green, WD3 3GG	WESTHE
2	SAS, Skipton, BD23 2TZ	SASQQQ
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=UPPER(LEFT(SUBSTITUTE(LEFT(A1,FIND(",",A1&",")-1)," ","")&"QQQQQQ",6))

 

[charliew]

THAT HAS WORKED PERFECTLY!!! thank you so much!!

 

[Peter_SSs]

If you are interested, I think that you can eliminate a small part of that formula as follows.

This has a slight difference too in that if there are any blank cells in the 'name' column this formula returns "" which I am assuming is preferable to "QQQQQQ"

BTW I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

20 04 21.xlsm
	A	B
1	West Herts GC, Croxley Green, WD3 3GG	WESTHE
2	SAS, Skipton, BD23 2TZ	SASQQQ
charliew

Cell Formulas
Range	Formula
B1:B2	B1	=UPPER(LEFT(SUBSTITUTE(SUBSTITUTE(A1," ",""),",","QQQQQ"),6))