Re: Clarification

Luke

I would split these using Data>Text to columns.... using space as the delimiter. This will split them into the 7/8 fields. Then you can re-assemble them using concatenation and copying down

e.g. M1234 and 2008000 will now be in A1 and B1, so you can re-assemble them in a helper column to the right using

=A1&" "&B1

Once you have them all re-assembled and copied down, copy the helper columns and Edit>Paste Special...>Values to themselves to kill the formulae and delete all the other stuff you don't need.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@xxxxxxxxxxxxxxxx
www.nickhodge.co.uk


"Luke Slotwinski" <LukeSlotwinski@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:ADEF0D94-AB8F-413F-A556-712C9F340B9E@xxxxxxxxxxxxxxxx
Sorry got home from work and realized I made a mistake in this description...
I need the paste string to be distributed accross a row not down a column...
i.e. A1 B1 C1 ect, and not A1 A2 A3... Sorry for the confusion.

"Luke Slotwinski" wrote:

I am in need of a solution to split a paste string into multiple cells within
a row.
There are 7 to 8 entries in this string, an example would be
M1234 2008000 Doe, Jane Mary 33Y(05/05/2000) F ER (8 string entry)
or
M5678 1234567 Lee, Jack 70Y(01/01/1900) M Psych (7 string entry)

Using the first example to expand on the needs of this project:
M1234 2008000 --> should go to Cell A1
Doe, Jane Mary --> should go to Cell A2
33Y(05/05/2000) F -> should go to Cell A3
ER --> to go to Cell A4
*what complicates matters is there is not always a middle name so the string
length varies from 7 and 8 entries (seperated by commas). But there will
always be an age (number) to start the next cell after the name.

Idealy if a user pasted this string of information into cell A1, it would
take it and split it up between A1 and A4.

Any help would be GREATLY appreciated,
Thank you,
Luke Slotwinski



.

Relevant Pages

  • Find text in column
    ... As part of a module I need to find the row of the cell in which a specified ... iRowNum is the row sought. ... sItem is the string to search for. ... There are several blanks cells between entries in the column. ...
    (microsoft.public.excel.programming)
  • RE: Continuity of the series
    ... If the number of entries in "G" series that you want to display is not ... Dim NewSet As String ... Dim NewSet1 As String ... If your series goes from a current cell down to the cell one above and ...
    (microsoft.public.excel.misc)
  • Re: Create four columns out of one
    ... How do you get John Smith from Carl Axling <vbg>? ... I'd select that column with all those entries (or just that one cell) ... > From that string I would like to create four columns like this, ...
    (microsoft.public.excel.misc)
  • Re: Count
    ... If a cell is empty then this cell will now only contain the empty ... string. ... clear any one of the entries in A1:A6 ...
    (microsoft.public.excel.misc)
  • VB, implementing the high score within this code
    ... Private possibleAs String ... Private Function SolvePuzzleAs Boolean ... ' Calculates the possible values for all the cell ...
    (microsoft.public.dotnet.languages.vb)
Loading