how to look for spaces?

[verhaba]

Hello

I am creating a small function that should create a new login for future users. The login is 7 characters long. Spaces in that name (in cell A1 eg) should be removed? But how do I do this?
How do I remove the space in the name: "De Castro Imelda". Apparently the function TRIM does not help me much. Either I use the wrong function, or I my syntax is not good...

Many thanks for your advice.

[Shijesh Kumar]

use substitute function

Please Login or Register  to view this content.

[JBeaucaire]

TRIM() only removes unnecessary spaces. It would leave at least 1 between existing words.

If A1 has the text string De Castro Imelda, that includes two spaces to remove. This formula will do that:
=SUBSTITUTE(A1, " ", "")
Or this to remove just one space:
=SUBSTITUTE(A1, " ", "", 1)

Now, if you only want to display 7 characters of that for your login, then perhaps:
=LEFT(SUBSTITUTE(A1, " ", ""), 7)

[verhaba]

Hey Thx,
That was rather quick. It works... but still... I have a question... Just image that I have a name with 2 spaces or even with more (think of a Spanish or Latino family name). I have an inbedded function like this:

=SUBSTITUTE(SUBSTITUTE(B8;" ";;1);" ";;1)

Unfortunately it only works for two spaces in the name... I wonder if you cannot let excel count how many spaces there are and remove them automatically. Or this more a case for Visual basic? Thx in advance

[JBeaucaire]

The first formula I gave will remove all the spaces.
=SUBSTITUTE(A1, " ", "")

[verhaba]

TRIM() only removes unnecessary spaces. It would leave at least 1 between existing words.

If A1 has the text string De Castro Imelda, that includes two spaces to remove. This formula will do that:
=SUBSTITUTE(A1, " ", "")
Or this to remove just one space:
=SUBSTITUTE(A1, " ", "", 1)

Now, if you only want to display 7 characters of that for your login, then perhaps:
=LEFT(SUBSTITUTE(A1, " ", ""), 7)

Hi,
Well the login should be in total 7 characters. These 7 characters should be made of
5 characters of the family name (from where the spaces are removed) and
2 characters from the name.
But if the family name is less then 5 characters (eg 3 characters) than the amount characters used for completing the login needs to be increased till the total amount of the characters of login has reached 7. In this case the amount of characters used on the name should be 4 in stead of 2, since the family name has only 3 characters... Hope you'll understand what I am saying... :-)

[JBeaucaire]

Nope. Simplest...provide 3-4 examples of text strings and the login you would think is correct based on those strings. That should make it clear.

[verhaba]

Nope. Simplest...provide 3-4 examples of text strings and the login you would think is correct based on those strings. That should make it clear.

Hi again,
these are a couple examples

family name - name --> login
Verhaegen - Bart --> verhaba
Li - Wangsu --> Liwangs
Van Gucht - Karel --> vanguka (spaces are left out)

Hope that you see where I am getting at...

[martindwilson]

i see that but there is no consistancy
Verhaegen - Bart --> verhaba 4 from first 3 from second =7
that sort of makes sense but why not 3 from first 4 from second or 5 from first 2 from second?
Li - Wangsu --> Liwangs all of first 5 from second =7
well thats sort of forced itself but coluld be Lwangsu
Van Gucht - Karel --> vanguka what determines how many you take from this one?? could just as easily be vangkar or vgukare
edit re read your post with rules!!!!!!!!
but how are they presented in cells i presume the - isnt there

[JBeaucaire]

So, as a logic thread, it goes:

1) If 3 words, remove first space to leave 2 words
2) If first word greater than or equal to 5 letters, take 5 characters from first word and 2 from last word to create login
3) If first word less than 5 letters, take all of first word and as many from second word to make 7-letter login.

Is that correct?

[JBeaucaire]

If your data is laid out the way you showed and my logic above is correct, then this will work:

Please Login or Register  to view this content.

The formula in C2 then copied down is:
=IF(LEN(SUBSTITUTE(A2, " ", ""))>=5, LOWER(LEFT(SUBSTITUTE(A2, " ", ""), 5) & LEFT(B2, 2)), LOWER(LEFT(SUBSTITUTE(A2, " ", "") & B2, 7)))

[martindwilson]

i went with this but it will fail if say name is li wan