Search for data in a specific format (1 Viewer)

[Gismo]

Hi All,

I have a spreadsheet with data populated from a maintenance program
2 of the coulombs are a combination of data but has not been separated by any punctuation.
data should look like below if only single references

if multiple references has been combined, below is the sample


the first line should be AMM and 65-11-00,6-11

how do i search for a format or a string of data then split it with a semicolon?

 

[Gasman]

The legnths of the data appear to be of constant lengths, so use Left,Mid etc.

 

[Gismo]

The legnths of the data appear to be of constant lengths, so use Left,Mid etc.

the length of the last few digits is not always the same, could be a 1 or 10, single or double digit
at first I also thought it was the same length and used left, mid function then realized some results where completely out of line
what i noticed from the spreadsheet, if you wrap the cell, it gives you the data as separate strings

how would I recognize this in access to split the data with a semicolon?
when imported into access, all the combined cell data is linked with not indication of a split

So I need to be able to enter a semicolon when there was a line break or a Alt-Enter

when the excel file is linked in access, you do not have the option to wrap the text to see multiple lines in a row

 

[jdraw]

...data populated from a maintenance program...

My recommendation is to fix the underlying issue. The maintenance program should be designed to provide the data in the proper format; and the receiver program should ensure the received data meets all validation/verification specs. It seems to me that these programs were never tested before being put into operational status. It is also possible that the data values and formatting have changed over time. Regardless, fix the underlying issue; don't accept and process and continue with a poorly designed transfer set up. Identify the proper record format and ensure both the sender and receiver programs are designed and tested and accepted.
Good luck.

 

[Gasman]

There must be a hidden character at the end.
I have always used Alt + Enter to put text on a new line in an Excel cell?

Can you not ask the people who give you this data, to present it in a more meaniful way, rather than just chucking a pile of characters at you.?
Even the comma is in the wrong place.

 

[Gismo]

There must be a hidden character at the end.
I have always used Alt + Enter to put text on a new line in an Excel cell?

Can you not ask the people who give you this data, to present it in a more meaniful way, rather than just chucking a pile of characters at you.?
Even the comma is in the wrong place.

63-11-00 is the chapter and the data after the comma represents the maintenance reference and line number

all the data in not total gibberish

i tried using the vbCRLf function but not sure if i used it correctly
if I can split all that was entered as Alt-Enter, it should work

 

[Gismo]

My recommendation is to fix the underlying issue. The maintenance program should be designed to provide the data in the proper format; and the receiver program should ensure the received data meets all validation/verification specs. It seems to me that these programs were never tested before being put into operational status. It is also possible that the data values and formatting have changed over time. Regardless, fix the underlying issue; don't accept and process and continue with a poorly designed transfer set up. Identify the proper record format and ensure both the sender and receiver programs are designed and tested and accepted.
Good luck.

i recon it is saved in a proper format as the program using the data has the separator, or rather, separate lines indicated

i just need to find a way to have the alt-enter function used to split my data with a punctuation mark

 

[Gismo]

isn't this one of those char(10) functions?

 

[theDBguy]

isn't this one of those char(10) functions?

Maybe. Can you post a sample Excel file?

 

[jdraw]

Can you provide some sample data so readers can see the issue in context?
OOps I see theDBGuy has requested the same.

 

[Gismo]

Maybe. Can you post a sample Excel file?

 

[Gismo]

how do i attach an excel file? when attaching i get a file not permitted

 

[theDBguy]

how do i attach an excel file? when attaching i get a file not permitted

Try zipping it first.

 

[Gasman]

how do i attach an excel file? when attaching i get a file not permitted

Zip it, rename the extension, but do tell us.
Go through each character and debug.print what it is ?

 

[Gasman]

63-11-00 is the chapter and the data after the comma represents the maintenance reference and line number

all the data in not total gibberish

i tried using the vbCRLf function but not sure if i used it correctly
if I can split all that was entered as Alt-Enter, it should work

View attachment 86180

I would beg to differ
There should be some sort of 'visible' character that would not be in the data that could be used as a delimiter.?

 

[Minty]

In Excel the line feed is written as Chr(10) , In access it needs Chr(13)&Chr(10) . USe

Replace([YourFieldName],Chr(10),Chr(13) & Chr(10))

 

[Gismo]

I would beg to differ
There should be some sort of 'visible' character that would not be in the data that could be used as a delimiter.?

this is the string
62-21-00 is the chapter
6-2 is the maintenance reference

 

[Gismo]

attached is the zip file

 

[Gismo]

In Excel the line feed is written as Chr(10) , In access it needs Chr(13)&Chr(10) . USe

Replace([YourFieldName],Chr(10),Chr(13) & Chr(10))

this seems to work perfect to determine the first string, how do i display all that is after Ch13 in the next string separated by a semicolon?
to display as:

65-11-00, 6-11; 65-11-00,6-13; 65-11-00,6-14

 

[Gismo]

In Excel the line feed is written as Chr(10) , In access it needs Chr(13)&Chr(10) . USe

Replace([YourFieldName],Chr(10),Chr(13) & Chr(10))

Oh, got it, thank you very much

Expr1: Replace([Documentation],Chr(10),Chr(13) & "; " & Chr(10))