On 7 Aug 2006 15:09:58 -0700, bill_nirl wrote:
Thank you for your reply fred, no use at all, but thank you anyway.
I have imported the spreadsheet into access (access 97 as i prefer to
work with it, less wizards than the newer versions) so that i may do an
export as a csv file or indeed keep the data in access to enable
quicker and easier data manipulation (and link directly to word as a
mail merge file if required)
The bit of vb code that i was looking would take the space as being the
deliminated point and use to the left of it as the cell in the table.
I have the code for using the right hand side of the space to give me
the Forenames, but was hoping for the left hand side (which is not as
simple as i thought).
Once i have worked out the module, i will then run and update query to
populate a new table and remove or archive the old table.
I thought this was a simple question and if anyone would like the code
to work out the right side of the space as a new field, you are welcome
to it.........
fredg wrote:
>On 7 Aug 2006 13:04:46 -0700, bill_nirl wrote:
>>Hi all, can anyone help. i have a spreasheet (set up by a looney)
they have in the surname field, the surname then forename. What sort
of code would i need to use to sort this out.
It looks like this:
|Smith Bill |14 Any Street|
|Doe John |19 High Stree|
and so on (there are 7891 of them). Would make life a lot easier to
run it in a module but cant for the life of me work out how to do it
(DUH!!!).
Thanks in advance.
Bill
bill underscore nirl at hotmail.com is my email :)
bi*******@hotmail.com or post it here :)
A cell?
Spreadsheet?
Excel?
You have posted this message to the wrong newsgroup.
The access in this groups name refers to Microsoft Access, a database
program.
Please repost to the correct newsgroup for the Excel program you are
using. I would suggest you include your Windows and Office version
number in the message.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
I would guess that the address (|14 Any Street|) is actually in a
different field.
If you are using Access, and ALL of the data is in the order of
LastName space FirstName, then:
LastName:Left([ullName],InStr([FullName]," ")-1)
FirstName:Mid([FullName],InStr([FullName]," ")+1)
Of course the above will fail if some of the names are like:
van der Meer Joseph
von Newman Carl
etc.
in which case you will need to alter your data and insert a comma
between last and first names.
Then you can use the above expressions, substituting "," for " " in
the above.
You could run a select query (which does not save the data)
or an Update query (which does), i.e:
Update YourTable Set YourTable.LastName =
Left([FullName],InStr([FullName]," ")-1), YourTable.FirstName =
Mid([FullName],InStr([FullName]," ")+1);
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
