Seperating cell contents into three cells 2

[scriptscribe]

Hi,

I've got a huge dump file from our mainframe computer that I have to fish through to find certain data. The dump file is comma delimited, but because it is effectively a dump of the code used in the system, some cells (including the cell containing the data that I want) are delimited by the mainframe with an "*". In short, the file is effectively "double-delimited."

The cells that I need to extract data from is in this format: 30000*1004*00

I need to get the 30000 in one column, the 1004 in another and the 00 in another.

Transferring the data to Excel is out of the question, as the file is over 700,000 rows.

Thanks for any help.

Mike

 

[GingerR]

if there are always 5 digits, then four, then 2, you can use left(), mid(), and right() to parse them out.

If the lengths of the data can be variable, throw instr() function in the mix which finds the "*".

You can goof around with those functions in a query to see how they work.

Here's some functions I threw together that will work for you. Make a new module and paste this into it:

Function ParseDataLeft(strData)
    'Get placement of first asterix. ParseDataLeft will be the characters to the left of that.
    Dim intPlacement As Integer 'Placement of first asterix
    intPlacement = InStr(strData, "*")
    ParseDataLeft = Left(strData, intPlacement - 1)
End Function

Function ParseDataMid(strData)
    'Set a temp string to strData minus the left portion which was determined above
    'Then get the left-most portion of that (to the left of the second asterix
    Dim intPlacement 'Placement of second asterix
    Dim strDataTemp As String
    strDataTemp = Right(strData, Len(strData) - Len(ParseDataLeft(strData)) - 1)
    intPlacement = InStr(strDataTemp, "*")
    ParseDataMid = Left(strDataTemp, intPlacement - 1)
End Function
Function ParseDataRight(strData)
    'ParseDataRight = right-most portion of string which is entire string minus two asterixes and the length
    'of ParseDataLeft + ParseDataMid
    ParseDataRight = Right(strData, Len(strData) - Len(ParseDataLeft(strData)) - Len(ParseDataMid(strData)) - 2)
End Function

Then call the functions, i.e. in a query that is based on your table, make three calcluated fields:

Expr1: ParseDataLeft([Data1])
Expr2: ParseDataMid([Data1])
Expr3: ParseDataRight([Data1])


Hope this helps. It was fun.

g

 

[dhookom]

You can use the Mid(), Instr(), Left(), and Right() functions to parse a string into separate values.

Why don't you check Help and make a stab at this and come back if you need more assistance?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[scriptscribe]

Ginger, I just added a quick "If IsNull()" section to each function and it worked perfectly! Thanks so much.

Duane, thanks for the tip.

 

[dhookom]

GingerR,
You sure are in a giving mood. Must be the season. I was hoping we could make scriptscribe do a little fishing before he was handed a fish ;-)

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[GingerR]

I started the fishing thing, but was bored so decided to goof around with it. Sometimes I can't help but pound out a quick one. I tried to cover with my "you can goof around with these in queries" line. I guess it didn't work

Hey Scriptscribe....Duane's right in that it would behoove you to learn how those functions work. You'll have my death on your shoulders if I see a similar post from you any time soon )

g

 

[PHV]

Another possibly interesting function (ac2k or above): Split

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244