Re: Clarification

One of those fascinating but oh so frustrating tasks unless you can trust the original data to be consistent! I just played around for a few minutes and came up with:
With your string in column A, Text to Columns fixed width could give you
M1234 in A, 1112223 in B and the rest in C. Then Text to Columns again on C, this time with comma as your delimiter will put the surname in C and the rest in D. Text to Columns again on D with ( as the delimiter leaves the forename(s)and age in D and the rest in E. Now insert a couple of columns between D and E. In E, put in a formula like
=IF(ISERROR(FIND(" ",TRIM(D1),FIND(" ",TRIM(D1),1)+1)),LEFT(TRIM(D1),FIND(" ",TRIM(D1))),LEFT(TRIM(D1),FIND(" ",TRIM(D1),FIND(" ",TRIM(D1),1)+1)))
which should give you one or two forenames. In F, =trim(RIGHT(D1,4)) will put the age there. Then Text to Columns on what's left with both space and ) as delimiters gives you the rest. If in stage 3 of the Text to Columns wizard you mark the left-hand column as "Date", your date-like data will turn into a date. All that's left then is to delete column D, and then concatenate as you will into columns further right.

Please try this on a copy of your data first, and remember that the long formula will only deal with one or two forenames (not three) and the little formula in F assumes that none of your patients is over 99.

--
Noel


"Luke Slotwinski" <LukeSlotwinski@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:F0622941-5DC9-4A3F-B06E-22722AFC8BCF@xxxxxxxxxxxxxxxx
Nick:

Thank you for your help... I am running into a problem though.
If I set the Text to Columns for an 8 entry string format...
M1234 1112223 SHMOE, JOE ADAM 2Y(01/01/2005) M ER
then I have an entry w/o a middle name that is 7 entries long
T2323 5551212 EXAMPLE, GIRL 3Y(01/01/2004) F AUC
It will paste 3Y(01/01/2004) into E* when I'd like to have it in F*... is
there a way to limit column E* to alpha only and if there is numerics to skip
to colum F and paste?

Thank you,
Luke Slotwinski
"Nick Hodge" wrote:

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

  • Re: Clarification
    ... Luke Slotwinski ... Columns delimited with space and comma as the delimiter. ... If I set the Text to Columns for an 8 entry string format... ... Jane Mary --> should go to Cell A2 ...
    (microsoft.public.excel.misc)
  • RE: string contra VBA code
    ... Optional Delimiter As String) As String ... Dim c, str As String, temp As String ... ' Results can be rubbish if a cell in data range is empry ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Programmers unpaid overtime.
    ... tokenizing a string correctly. ... ability to specify a set of delimiter tokens, ... >> postmodern attack on language itself, ... An the scientists, in turn, think a lot of that artistic talk about ...
    (comp.programming)
  • Re: Surprise in StrConv using vbProperCase
    ... > Your code will capitalize the first letter of a string. ... Use Split(string into array of words using blank as delimiter); ...
    (microsoft.public.access.modulesdaovba)
  • Re: VB vs C#
    ... Dim delimiter As String ... Dim testname As String ...
    (microsoft.public.dotnet.general)
Loading