A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Seperating information in a cell



 
 
Thread Tools Display Modes
  #1  
Old August 4th, 2008, 09:26 PM posted to microsoft.public.excel.worksheet.functions
AndyK
external usenet poster
 
Posts: 19
Default Seperating information in a cell

What formula can be used to sepearte information in a cell and put into
several cells? I exported infromation contained in a pdf file into Excel. In
one of the columns each cell has a full name, Street address, City, state and
zip. I want to have first name, last name, street address, city, state and
zip extracted and put into different columns. Can this be done?
  #2  
Old August 4th, 2008, 09:38 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\)[_1025_]
external usenet poster
 
Posts: 1
Default Seperating information in a cell

How is the full name shown in the text... last namecomma first name or
simple first name last name? Are there ever any middle names or titles
(Mr., Mrs, Dr., etc.)?

Rick


"andyK" wrote in message
...
What formula can be used to sepearte information in a cell and put into
several cells? I exported infromation contained in a pdf file into Excel.
In
one of the columns each cell has a full name, Street address, City, state
and
zip. I want to have first name, last name, street address, city, state and
zip extracted and put into different columns. Can this be done?


  #3  
Old August 4th, 2008, 09:56 PM posted to microsoft.public.excel.worksheet.functions
AndyK
external usenet poster
 
Posts: 19
Default Seperating information in a cell

The Name is shown in each cell like the following:

First Name Middle Initial Last Name

(There are no commas just a space between First Middle and Last name

"Rick Rothstein (MVP - VB)" wrote:

How is the full name shown in the text... last namecomma first name or
simple first name last name? Are there ever any middle names or titles
(Mr., Mrs, Dr., etc.)?

Rick


"andyK" wrote in message
...
What formula can be used to sepearte information in a cell and put into
several cells? I exported infromation contained in a pdf file into Excel.
In
one of the columns each cell has a full name, Street address, City, state
and
zip. I want to have first name, last name, street address, city, state and
zip extracted and put into different columns. Can this be done?



  #4  
Old August 4th, 2008, 10:04 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\)[_1026_]
external usenet poster
 
Posts: 1
Default Seperating information in a cell

What do you put if there is no middle initial (like for my wife... she
doesn't have a middle name)? The reason I ask is if you put nothing, then it
will be hard to tell the difference between these to names (I know people
with names like these)...

Mary Anne Jones (Mary Anne is her full first name)

Tom Della Rossa (Della Rossa is his full last name)

Rick


"andyK" wrote in message
...
The Name is shown in each cell like the following:

First Name Middle Initial Last Name

(There are no commas just a space between First Middle and Last name

"Rick Rothstein (MVP - VB)" wrote:

How is the full name shown in the text... last namecomma first name
or
simple first name last name? Are there ever any middle names or
titles
(Mr., Mrs, Dr., etc.)?

Rick


"andyK" wrote in message
...
What formula can be used to sepearte information in a cell and put into
several cells? I exported infromation contained in a pdf file into
Excel.
In
one of the columns each cell has a full name, Street address, City,
state
and
zip. I want to have first name, last name, street address, city, state
and
zip extracted and put into different columns. Can this be done?




  #5  
Old August 4th, 2008, 10:12 PM posted to microsoft.public.excel.worksheet.functions
AndyK
external usenet poster
 
Posts: 19
Default Seperating information in a cell

If there is no middle initial then nothing goes as They wanted the first name
column to have either First name alone or First name than a space and then
middle initial

Thanks, Andy

"Rick Rothstein (MVP - VB)" wrote:

What do you put if there is no middle initial (like for my wife... she
doesn't have a middle name)? The reason I ask is if you put nothing, then it
will be hard to tell the difference between these to names (I know people
with names like these)...

Mary Anne Jones (Mary Anne is her full first name)

Tom Della Rossa (Della Rossa is his full last name)

Rick


"andyK" wrote in message
...
The Name is shown in each cell like the following:

First Name Middle Initial Last Name

(There are no commas just a space between First Middle and Last name

"Rick Rothstein (MVP - VB)" wrote:

How is the full name shown in the text... last namecomma first name
or
simple first name last name? Are there ever any middle names or
titles
(Mr., Mrs, Dr., etc.)?

Rick


"andyK" wrote in message
...
What formula can be used to sepearte information in a cell and put into
several cells? I exported infromation contained in a pdf file into
Excel.
In
one of the columns each cell has a full name, Street address, City,
state
and
zip. I want to have first name, last name, street address, city, state
and
zip extracted and put into different columns. Can this be done?




  #6  
Old August 4th, 2008, 10:50 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\)[_1029_]
external usenet poster
 
Posts: 1
Default Seperating information in a cell

I think you added something new from your first post.... if there is a
middle initial, it is to be linked with the first name and placed in the
first name column, is that correct? Three more questions...

1) Is the state name a 2-character abbreviation or is it spelled out fully?

2) Is the zip code (which I presume you want in its own column) **always**
5-digits long or could it be with the dash and additional 4 digit tacked on?

3) Is all the data separated by a commaspace as you show (except for the
zip code which I presume is just separated from the state name by a single
space)?

Rick


"andyK" wrote in message
...
If there is no middle initial then nothing goes as They wanted the first
name
column to have either First name alone or First name than a space and then
middle initial

Thanks, Andy

"Rick Rothstein (MVP - VB)" wrote:

What do you put if there is no middle initial (like for my wife... she
doesn't have a middle name)? The reason I ask is if you put nothing, then
it
will be hard to tell the difference between these to names (I know people
with names like these)...

Mary Anne Jones (Mary Anne is her full first name)

Tom Della Rossa (Della Rossa is his full last name)

Rick


"andyK" wrote in message
...
The Name is shown in each cell like the following:

First Name Middle Initial Last Name

(There are no commas just a space between First Middle and Last name

"Rick Rothstein (MVP - VB)" wrote:

How is the full name shown in the text... last namecomma first
name
or
simple first name last name? Are there ever any middle names or
titles
(Mr., Mrs, Dr., etc.)?

Rick


"andyK" wrote in message
...
What formula can be used to sepearte information in a cell and put
into
several cells? I exported infromation contained in a pdf file into
Excel.
In
one of the columns each cell has a full name, Street address, City,
state
and
zip. I want to have first name, last name, street address, city,
state
and
zip extracted and put into different columns. Can this be done?





  #7  
Old August 5th, 2008, 12:23 AM posted to microsoft.public.excel.worksheet.functions
AndyK
external usenet poster
 
Posts: 19
Default Seperating information in a cell

Yes that is correct with the middle initial. State is two characters, zip is
5 numbers, all information is only seperated by a space except city and state
which is by a comma.


"Rick Rothstein (MVP - VB)" wrote:

I think you added something new from your first post.... if there is a
middle initial, it is to be linked with the first name and placed in the
first name column, is that correct? Three more questions...

1) Is the state name a 2-character abbreviation or is it spelled out fully?

2) Is the zip code (which I presume you want in its own column) **always**
5-digits long or could it be with the dash and additional 4 digit tacked on?

3) Is all the data separated by a commaspace as you show (except for the
zip code which I presume is just separated from the state name by a single
space)?

Rick


"andyK" wrote in message
...
If there is no middle initial then nothing goes as They wanted the first
name
column to have either First name alone or First name than a space and then
middle initial

Thanks, Andy

"Rick Rothstein (MVP - VB)" wrote:

What do you put if there is no middle initial (like for my wife... she
doesn't have a middle name)? The reason I ask is if you put nothing, then
it
will be hard to tell the difference between these to names (I know people
with names like these)...

Mary Anne Jones (Mary Anne is her full first name)

Tom Della Rossa (Della Rossa is his full last name)

Rick


"andyK" wrote in message
...
The Name is shown in each cell like the following:

First Name Middle Initial Last Name

(There are no commas just a space between First Middle and Last name

"Rick Rothstein (MVP - VB)" wrote:

How is the full name shown in the text... last namecomma first
name
or
simple first name last name? Are there ever any middle names or
titles
(Mr., Mrs, Dr., etc.)?

Rick


"andyK" wrote in message
...
What formula can be used to sepearte information in a cell and put
into
several cells? I exported infromation contained in a pdf file into
Excel.
In
one of the columns each cell has a full name, Street address, City,
state
and
zip. I want to have first name, last name, street address, city,
state
and
zip extracted and put into different columns. Can this be done?






  #8  
Old August 5th, 2008, 01:57 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\)[_1031_]
external usenet poster
 
Posts: 1
Default Seperating information in a cell

??? The street address is separated from the name section by only a single
space? And the street address is separated from the city by only a single
space? No commas?? Really??? Since street addresses can be made up of more
than one word (discounting the number, assuming it has a number) and city
names can be made up of more than one word and since we don't have a lock on
multi-part names with middle initials either there or not there, I would say
this just went from a doable (better than 99% of the time) to damn near
impossible to do any more. Are you positive the name, address and city names
sections are only separated by a **single** space?

Rick


"andyK" wrote in message
...
Yes that is correct with the middle initial. State is two characters, zip
is
5 numbers, all information is only seperated by a space except city and
state
which is by a comma.


"Rick Rothstein (MVP - VB)" wrote:

I think you added something new from your first post.... if there is a
middle initial, it is to be linked with the first name and placed in the
first name column, is that correct? Three more questions...

1) Is the state name a 2-character abbreviation or is it spelled out
fully?

2) Is the zip code (which I presume you want in its own column)
**always**
5-digits long or could it be with the dash and additional 4 digit tacked
on?

3) Is all the data separated by a commaspace as you show (except for
the
zip code which I presume is just separated from the state name by a
single
space)?

Rick


"andyK" wrote in message
...
If there is no middle initial then nothing goes as They wanted the
first
name
column to have either First name alone or First name than a space and
then
middle initial

Thanks, Andy

"Rick Rothstein (MVP - VB)" wrote:

What do you put if there is no middle initial (like for my wife... she
doesn't have a middle name)? The reason I ask is if you put nothing,
then
it
will be hard to tell the difference between these to names (I know
people
with names like these)...

Mary Anne Jones (Mary Anne is her full first name)

Tom Della Rossa (Della Rossa is his full last name)

Rick


"andyK" wrote in message
...
The Name is shown in each cell like the following:

First Name Middle Initial Last Name

(There are no commas just a space between First Middle and Last name

"Rick Rothstein (MVP - VB)" wrote:

How is the full name shown in the text... last namecomma first
name
or
simple first name last name? Are there ever any middle names or
titles
(Mr., Mrs, Dr., etc.)?

Rick


"andyK" wrote in message
...
What formula can be used to sepearte information in a cell and
put
into
several cells? I exported infromation contained in a pdf file
into
Excel.
In
one of the columns each cell has a full name, Street address,
City,
state
and
zip. I want to have first name, last name, street address, city,
state
and
zip extracted and put into different columns. Can this be done?







  #9  
Old August 5th, 2008, 05:54 PM posted to microsoft.public.excel.worksheet.functions
AndyK
external usenet poster
 
Posts: 19
Default Seperating information in a cell

Yes. This is not for something we will have to do all the time. It was for a
pdf file that I converted and exported into Excel and need to have these
items seperated so that we can sort by different items.

Thanks Andy

"Rick Rothstein (MVP - VB)" wrote:

??? The street address is separated from the name section by only a single
space? And the street address is separated from the city by only a single
space? No commas?? Really??? Since street addresses can be made up of more
than one word (discounting the number, assuming it has a number) and city
names can be made up of more than one word and since we don't have a lock on
multi-part names with middle initials either there or not there, I would say
this just went from a doable (better than 99% of the time) to damn near
impossible to do any more. Are you positive the name, address and city names
sections are only separated by a **single** space?

Rick


"andyK" wrote in message
...
Yes that is correct with the middle initial. State is two characters, zip
is
5 numbers, all information is only seperated by a space except city and
state
which is by a comma.


"Rick Rothstein (MVP - VB)" wrote:

I think you added something new from your first post.... if there is a
middle initial, it is to be linked with the first name and placed in the
first name column, is that correct? Three more questions...

1) Is the state name a 2-character abbreviation or is it spelled out
fully?

2) Is the zip code (which I presume you want in its own column)
**always**
5-digits long or could it be with the dash and additional 4 digit tacked
on?

3) Is all the data separated by a commaspace as you show (except for
the
zip code which I presume is just separated from the state name by a
single
space)?

Rick


"andyK" wrote in message
...
If there is no middle initial then nothing goes as They wanted the
first
name
column to have either First name alone or First name than a space and
then
middle initial

Thanks, Andy

"Rick Rothstein (MVP - VB)" wrote:

What do you put if there is no middle initial (like for my wife... she
doesn't have a middle name)? The reason I ask is if you put nothing,
then
it
will be hard to tell the difference between these to names (I know
people
with names like these)...

Mary Anne Jones (Mary Anne is her full first name)

Tom Della Rossa (Della Rossa is his full last name)

Rick


"andyK" wrote in message
...
The Name is shown in each cell like the following:

First Name Middle Initial Last Name

(There are no commas just a space between First Middle and Last name

"Rick Rothstein (MVP - VB)" wrote:

How is the full name shown in the text... last namecomma first
name
or
simple first name last name? Are there ever any middle names or
titles
(Mr., Mrs, Dr., etc.)?

Rick


"andyK" wrote in message
...
What formula can be used to sepearte information in a cell and
put
into
several cells? I exported infromation contained in a pdf file
into
Excel.
In
one of the columns each cell has a full name, Street address,
City,
state
and
zip. I want to have first name, last name, street address, city,
state
and
zip extracted and put into different columns. Can this be done?








  #10  
Old August 5th, 2008, 06:15 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\)[_1034_]
external usenet poster
 
Posts: 1
Default Seperating information in a cell

Is there any chance you can export the data again using a consistent
delimiter (a commaspace for example) between the name field and address,
between the between address and city?

Rick


"andyK" wrote in message
...
Yes. This is not for something we will have to do all the time. It was for
a
pdf file that I converted and exported into Excel and need to have these
items seperated so that we can sort by different items.

Thanks Andy

"Rick Rothstein (MVP - VB)" wrote:

??? The street address is separated from the name section by only a
single
space? And the street address is separated from the city by only a single
space? No commas?? Really??? Since street addresses can be made up of
more
than one word (discounting the number, assuming it has a number) and city
names can be made up of more than one word and since we don't have a lock
on
multi-part names with middle initials either there or not there, I would
say
this just went from a doable (better than 99% of the time) to damn near
impossible to do any more. Are you positive the name, address and city
names
sections are only separated by a **single** space?

Rick


"andyK" wrote in message
...
Yes that is correct with the middle initial. State is two characters,
zip
is
5 numbers, all information is only seperated by a space except city and
state
which is by a comma.


"Rick Rothstein (MVP - VB)" wrote:

I think you added something new from your first post.... if there is a
middle initial, it is to be linked with the first name and placed in
the
first name column, is that correct? Three more questions...

1) Is the state name a 2-character abbreviation or is it spelled out
fully?

2) Is the zip code (which I presume you want in its own column)
**always**
5-digits long or could it be with the dash and additional 4 digit
tacked
on?

3) Is all the data separated by a commaspace as you show (except
for
the
zip code which I presume is just separated from the state name by a
single
space)?

Rick


"andyK" wrote in message
...
If there is no middle initial then nothing goes as They wanted the
first
name
column to have either First name alone or First name than a space
and
then
middle initial

Thanks, Andy

"Rick Rothstein (MVP - VB)" wrote:

What do you put if there is no middle initial (like for my wife...
she
doesn't have a middle name)? The reason I ask is if you put
nothing,
then
it
will be hard to tell the difference between these to names (I know
people
with names like these)...

Mary Anne Jones (Mary Anne is her full first name)

Tom Della Rossa (Della Rossa is his full last name)

Rick


"andyK" wrote in message
...
The Name is shown in each cell like the following:

First Name Middle Initial Last Name

(There are no commas just a space between First Middle and Last
name

"Rick Rothstein (MVP - VB)" wrote:

How is the full name shown in the text... last namecomma
first
name
or
simple first name last name? Are there ever any middle names
or
titles
(Mr., Mrs, Dr., etc.)?

Rick


"andyK" wrote in message
...
What formula can be used to sepearte information in a cell and
put
into
several cells? I exported infromation contained in a pdf file
into
Excel.
In
one of the columns each cell has a full name, Street address,
City,
state
and
zip. I want to have first name, last name, street address,
city,
state
and
zip extracted and put into different columns. Can this be
done?









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:19 PM.


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