Add string only if a text is present in a cell

[Solved/Closed]
Report
-
 Deloro -
Hello, i have this string =CONCATENATE(MID(B5;1;1);".")

i use to have the initial of a name like this: from "Name" to "N."
but some fields have no text and this formula gives me just the dot as result.

how can i not have the dot and have the result only if B5 have text in it?

thanks


2 replies

Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
Deloro, Good afternoon.

Try to use :

=IF(B5<>"";CONCATENATE(MID(B5;1;1);".");"")


Is this what you want?
I hope it helps.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Perfect thanks! just what i wanted! It works like a charm :)
i have one similar problem tho:

now i have his string:

=CONCATENATE(CONCATENATE(UPPER(A55));(CONCATENATE(UPPER(B55);" ";UPPER(C55))))

the fields contain names:
name 1 (A55), name 2 (B55) and surname (C55)

with name 1 and surname i have te result as: Name1 Surname
but with name 1 name 2 and surname i have this: Name1Name2 Surname

so i need a space between name 1 and name 2. how can i have a space after name 1 (or before name 2) only if name 2 is present? (i tried but it adds a double space if name 2 is not present..)

i can't figure out how to write it..


Thanks
> Deloro
I figured it out!

=CONCATENATE(CONCATENATE(UPPER(IF(D55<>"";CONCATENATE(UPPER(C55);" ");CONCATENATE(UPPER(C55))));IF(D55<>"";CONCATENATE(UPPER(D55);" ");" ");UPPER(E55)))
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
Deloro, Good morning.

Please, open another post to present your new question.
Using a SOLVED post With another question, dificult people to help you.

Have a nice day.
you're right. i actually let the post marked as unsolved until i found the solution myself.

but yeah, i made a little mess. i'm sorry :c

maybe it's possible to split the questions into 2 threads?
they might be helpful for other people.