Help with textfunction

[silentwolf]

HI guys,

I am struggling to get the function to work to find me the position of the 4th last space within a string from the right.

String is like.. "Firstname Secondname Surname Myhome 23 8545 Westfield"
but it also could be like "Firstname Surname Myhome 5 4524 Testinghof"

Now I like to get rid of the adresses within the string..

So basically the result need to be like: "Firstname Secondname Surname"
or " Firstname Surname"

I was thinking of finding the 4th last space within the string. Tried with Find and substitute but as the name or better the empty spaces can vary I was out of luck getting it to work.

Could someone give me a hand with this please?

many thanks

Albert

 

[Fluff]

One option with a UDF

Function GetFullName(Cl As Range) As String
   Dim Ary As Variant, i As Long, Tmp As String
   
   Ary = Split(Cl, " ")
   For i = 0 To UBound(Ary) - 4
      Tmp = Tmp & " " & Ary(i)
   Next i
   GetFullName = Trim(Tmp)
End Function

Excel 2013/2016
	A	B
8	Firstname Secondname Surname Myhome 23 8545 Westfield	Firstname Secondname Surname
9	Firstname Surname Myhome 5 4524 Testinghof	Firstname Surname
List

Cell Formulas
Range	Formula
B8	=GetFullName(A8)

 

[mikerickson]

With text in A1
=LEN(A1)-LEN(SUBSTITUTE(A1," ","") is the number of spaces in the string

=(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3 is the space after the name part

=SUBSTITUTE(A1," ",REPT(" ",255),(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3) pads the key space to 255 spaces.

=LEFT(SUBSTITUTE(A1," ",REPT(" ",255),(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3)) returns the first part of that padded string.
Then TRIM can remove the trailing spaces.

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",255),(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-3)),255))

 

[silentwolf]

Hi to all!

Thank you for your help!!
Fantastic

Cheers!!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[silentwolf]

Hi Fluff,

as always there is one which is out of the norm.. what if there is a a text like.. "Firstname Secondname Surname Myhome 11 5656 Mytown Newtown"
or "Firstname Surname Myhome 12 5656 Mytown Newtown"

how would I need to change it to suit?

Thanks for your help!

 

[silentwolf]

Oh no there is another problem..

An address also can be something like that..."My Company and Anothercompany In Mythome 1 5555 Mytown"
so that means "In Myhome 1 5555 Mytown" is the address .(

 

[Fluff]

Unless there is a uniform way of ascertaining the data you need it will be very difficult (if not impossible).

 

[silentwolf]

Hi Fluff,
thanks for your input! Yes I know it is difficult but I try find a different way for the rest of the data..

However I do have one more question you might be able to answer.

How can I check if the array has only one item in it ...?
So if there is only one item in the array then exit sub..

function(s as string) as string
dim ary as variant
dim i as long
dim tmp as string

ary=split(s, "")

so hier how to I check if the array has not " " so it can not be split
and if it does then

ary= s

end function

 

[Fluff]

How about

Function GetFullName(Cl As Range) As String
   Dim Ary As Variant, i As Long, Tmp As String
   
   Ary = Split(Cl, " ")
   If UBound(Ary) <= 3 Then
      GetFullName = Cl.Value
   Else
      For i = 0 To UBound(Ary) - 4
         Tmp = Tmp & " " & Ary(i)
      Next i
      GetFullName = Trim(Tmp)
   End If
End Function