alphanumeric strings

[reporter1]

Hi Excel Pros,

I'm no excel wizard and I'm having difficulty separating strings of text and numbers from one cell into separate columns.

Here's an example:

MONSEF,BERNADETTE03/25/910930DMRAAM10-t/f1000150012001730

I'd like the information separated as follows:

MONSEF,BERNADETTE 03/25/91 0930 DMRA AM10-t/f 1000 1500 1200 1730

Each row has different length names, dates, times etc...

Is this even possible to accomplish with a formula?

Dizzy,
reporter1

 

[-Fabez-]

So you would like to split a string into smaller strings, by finding a series of charactars and splitting at that point ? If this is not what you meant could you elaborate please

 

[reporter1]

Fabez,

Yes, but with some strings being different lengths, I can't do delimited text to columns. Here's another example:

LOPRIORE,JOHN09/27/941200PM10-fss1000173014451945

Should read:

LOPRIORE,JOHN 09/27/94 1200 PM10-FSS 1000 1730 1445 1945

The last four sets of numbers are all made up of 4 characters (1000, 1730, 1445, 1945)

Is there help for me?

 

[-Fabez-]

Where is the data generated as it would be easier to include a seperator string when it is made. If you have no control over its making then it would be best to use wild cards and generic strings to search and split the main string.

 

[reporter1]

Fabez,
The PDF file is created by someone else so I can't change the original format. I'm not very familiar with wild cards and generic strings. How would I get each string to separate at points where numbers meet numbers if the number of characters is different? Is the wild card used for numbers that are all the same in length?

Thanks,
reporter1

 

[OBP]

Are some of those numbers always the same length?
Like the last 4 for instance?

 

[OBP]

This might be possible using VBA.

 

[reporter1]

Yes, the last four sets of numbers are all four characters.

 

[OBP]

Would you want to seperate the Second name from the comma with a space?

 

[reporter1]

I don't know how to do VBA. Wish I did!

 

[reporter1]

No need to add spacing for the names

 

[-Fabez-]

Okay then. A wild card is something that can represent anything, like * does in Actionscript, a generic string is a general string like, dd / mm / yy.

LOPRIORE,JOHN09/27/941200PM10-fss1000173014451945

LOPRIORE,JOHN 09/27/94 1200 PM10-FSS 1000 1730 1445 1945

In order to achieve the seperation on this string you would have to follow several steps.

I could either tell you how to do this in Excell or another language if you want

 

[OBP]

If it can wait until tomorrow and none of the Excel Guys come along I can create you some VBA that should do it.

 

[OBP]

If Fabez doesn't do it for you let me know.

 

[reporter1]

I think I'd prefer excel so I can learn something from it in case something similar occurs again in the future. It's no emergency - it can wait until Monday. I'll be leaving here (work) in 20 minutes.

Thanks -
reporter 1