Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default Transfer specific characters from cell to another cell.

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.

  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,886
Default Transfer specific characters from cell to another cell.

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 DataText to
columnDelimitedseparator CommaFinish

Dependant upon whether there are names and initials, this might give you
a spilt that you can work with.
--
Regards

Roger Govier


"Willing to learn" <Willing to wrote in
message ...
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.



  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 527
Default Transfer specific characters from cell to another cell.

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

"Willing to learn" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 527
Default Transfer specific characters from cell to another cell.

Alternatively, you can use this UDF pasted into a VB Module; ALT +
F11InsertModule

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
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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automating cell to transfer to another cell WANTED Excel Discussion (Misc queries) 1 May 23rd 07 04:27 PM
Link from a specific Cell in Excel to a specific para. in Word CathyK Excel Worksheet Functions 0 August 10th 06 04:40 PM
Lock data in a cell a specific cell based on selection on other ce CrimsonPlague29 Excel Worksheet Functions 0 May 10th 06 11:06 AM
how do I highlite text within a cell (specific characters) tim Excel Discussion (Misc queries) 1 May 20th 05 05:23 AM
how do I make a word typed in a cell go to a specific cell in anot Lmatarazzo Excel Discussion (Misc queries) 3 April 21st 05 04:29 AM


All times are GMT +1. The time now is 06:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"