Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automating cell to transfer to another cell | Excel Discussion (Misc queries) | |||
Link from a specific Cell in Excel to a specific para. in Word | Excel Worksheet Functions | |||
Lock data in a cell a specific cell based on selection on other ce | Excel Worksheet Functions | |||
how do I highlite text within a cell (specific characters) | Excel Discussion (Misc queries) | |||
how do I make a word typed in a cell go to a specific cell in anot | Excel Discussion (Misc queries) |