Transfer specific characters from cell to another cell.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, I have this sheet that has 700 cells which includes name, last name of
the person and his/her address in it.

note: Name, last name and address are in the same cell.

Now I would like to transfer JUST the names and last name to another cell.

Is it possible to do that? if so, how?
I don't want to erase 700 address of each cell and then copy and paste the
name/last name. That will take a lot of time.

Could somebody guide me? please, I am willing to learn.

Regards.
 
Hi

What is the separator between Name Lastname and Address?
Is it space, comma or something else?

Assuming a Comma, you may be able to use Data>Text to
column>Delimited>separator Comma>Finish

Dependant upon whether there are names and initials, this might give you
a spilt that you can work with.
 
Rogers method is great if the text is sepated by a comma. If not you could
use the SUbstitute function to insert a "," after the nth space. see data in
A1:A4

John Atherton 10 Crescent Close
Fred Smith 24 The Hi Road
James Graham, 16 the Low Road
Mr James Spade The Bronx

In B1 type =SUBSTITUTE(A7," ",", ",2) and copy down. This results in

John Atherton, 10 Crescent Close
Fred Smith, 24 The Hi Road
James Graham,, 16 the Low Road
Mr James, Spade The Bronx

You can see that JAmes Graham now has two comas and James Spade has a coma
after his first name. What I'm saying is that it is awkward to give a
solution that will cover every situtation. Still, after converting the
formulas to value you cuold then use Data, Text to Column

Providing that there are no titles (Mr, Mrs, Ms et al) then you could use a
formula to extract the Names with the Left function.

To extract the First NAme only use
=Left(a1,Find(" ",a1)-1)
First 2 Names
=LEFT($A1,FIND(" ",$A1,FIND(" ",$A1)+1)-1)
The Last Name
=MID(A1,FIND(" ",A1)+1,FIND(" ",$A1,FIND(" ",$A1,FIND(" ",$A1)+1))-FIND("
",A1))
The rest of the address
=TRIM(RIGHT(A1,LEN(A1)-FIND(" ",$A1,FIND(" ",$A1)+1)))

Remember to convert results to values and you have a few tools to work
through your list.

Regards
Peter
 
Alternatively, you can use this UDF pasted into a VB Module; >ALT +
F11>Insert>Module

Function GetWord(ByVal txt, Optional start As Integer, Optional q As
Integer) As String
Dim tmp As String, i As Integer, str() As String
txt = Application.Substitute(txt, ",", "")
str() = Split(txt, " ")
If start = 0 Then
'Just remove commas
GetWord = txt 'this is OK
ElseIf start >= 1 And q > 1 Then
For i = start - 1 To start + q - 2
tmp = tmp & str(i) & " "
Next i
GetWord = Trim(tmp)
Exit Function
ElseIf start = 1 And q = 0 Then
GetWord = str(start - 1)
End If
End Function

if A15 contains:= James Graham, 16 the Low Road

then =getword(A15) returns:
James Graham, 16 the Low Road (commas removed)

=getword(A15,1,2) returns:
James Graham

=getword(A15,3,4) returns:
16 the Low Road

and Getword(A15,1) returns:
James

you should be able to build a good list with these formulas.

Best of luck
Peter
 
Back
Top