on 07-15-2017 7:57 AM
hi Experts,
I have three columns Name1, Name2,Name3
Name 1 contain user name
Name 2 contain user name or user number(id-numeric value)
Name 3 contain user name or number(id numeric value)
I want to add/concatenate only all the name, if any column contain number then I need to exclude, for example
Name 1 - Ram
Name 2 - Kumar
Name 3 - 101
final expected output - Ram Kumar, I need to exclude 101 and concatenate other two values
Name 1 - James
Name 2 - 102
Name 3 - 103
final expected output - James, I need to exclude 102 and 103 and need to display only James
kindly provide your inputs
Regards
Balaji
Hi Balaji,
Try using the ToNumber() function. Try this, if the tonumber works, then we don't need to concat it, else we know its a string and need to concat it. The Trim on the outside is to remove any spaces in the beginning or the end.
=Trim((If(Iserror(ToNumber([Name 1])=0) Then [Name 1] Else "") + " " + (If(Iserror(ToNumber([Name 2])=0) Then [Name 2] Else "") + (If(Iserror(ToNumber([Name 3])=0) Then [Name 3] Else ""))
Thankss,
Mahboob Mohammed
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.