Separate out information that has been typed into one field?

[slenish]

I imported some data from an excel spreadsheet to a table in Access. There is one field where they have two pieces of information typed into one column that i want broken in to two columns. Is it possible to write a code or validation rule to automatically seperate the information with out having to go back and do it by hand?

for example;

Column 1
[Name 1 Frank] (info)

I want to break this in to two columns

[Colm 1]......[Colm 2]
[Name 1]....[Frank]

Just wondering if it can be done because going back and entering in a thousand of these by hand is really time consuming.

[TheSmileyCoder]

Would depend on the date. You can do alot of seperating if you can find a valid criteria for the whole dataset. Try to supply us with some real test data, and we can look at it.

The easiest is if there is any delimiter used between the 2 data types, then you can use Split.

[NeoPa]

As Smiley says, you need to have a clear understanding of what criteria to use to determine where the split should occur. Is it on a number of characters? Is it on a separator character?

Your example data wouldn't be obvious as the spaces between your fields are not the only spaces. It would split it [Colm],[1],[Colm],[2], etc.

[slenish]

I do have a clearn understanding of where to split. First part starts with Box and then a number like (Box 1) then after that is a name like Jeff.

The word Box always stays the same but the number next to box changes and goes up by one each time. Also the name next to box could be the same for so many records and then change. so there is a good dividing point.

There are no dates next to the records only thing I can see that stays constant is an account number. So maybe I could make it pull account number 5 column Shipper then split box 1 from name Jeff....

What do you think??

[NeoPa]

If that's your clear understanding then we have a problem. It doesn't match your example data. For us then, it is far from clear. I would hesitate to proceed on this basis.

Your third paragraph makes no sense to me at all. In the context of the example data provided I see nothing that remotely matches what you say.

This may simply be the example data is poorly chosen, but to proceed it is important that what you say matches the data so that we can get a clear understanding (if we're to be of any reliable help).

[topher23]

This is a classic exercise in string manipulation. Since you're splitting a string in half, you're going to need to use at least 3 string functions: Left, Right and InStrRev (or InStr). Look up the functions in Access help to get an idea of how they work. You'll end up with two statements: one that gives you 'Box 1' and one that gives you 'Jeff'

If "Box 1 Jeff" as in your example is the ONLY thing in this field, your expressions will end up looking something like

Left([FieldName],InStrRev([FieldName]," ")-1)
Right([FieldName],InStrRev([FieldName]," ")-2)

where [FieldName] is your actual field. In the case of "Box 1 Jeff", the first expression produces "Box 1" and the second expression produces "Jeff"

Hope this is helpful!

[slenish]

Nice example topher23.

The only question I have now is where would I put that code? Im not using a form im just directly taking a downloaded excel file and importing it in to a table so would i put that as a validation rule??


NeoPa,
My appologies on the third paragraph Im not sure what i started writing there. I should have just left the third para out. Didnt really need it.

I have another problem that im trying to deal with that is more urgent than this. I am trying to create an if statement for a number box on my form. What I want to do is make it so you have to enter in at least 5 numbers in to the box no more no less. I have it half working where if you enter in more than 5 you get an error message but less than 5 does nothing. Here is what i have so far.

Dim number, zipCode As Integer
Dim error As String
 
#Const number = 0
zipCode = 5
 
If number < zipCode 
error = " Not enough numbers. \n"
 
ElseIf number > zipCode Then
error = " To many numbers in field. \n"
End If
'End If

[NeoPa]

@topher23
In fact Smiley has already suggested a solution that uses only the one function (Split()) which is perfectly viable.

What we are simply waiting for now is a clear and consistent definition of what determines the split. None of these solutions works well if the count of the characters is what's used to split the data.

[NeoPa]

@slenish
That's not a problem Slenish. However, the situation is still quite unclear due to the example not matching your explanation.
@slenish
This may not be answered here. New questions require separate threads. On the plus side, that is quite easy for you to do.

[slenish]

Im guessing that it cant be done. Because there is no other way to seperate the information except for the count of characters, and that is not the best because the count can change. It does not stay constant. There could be between 11 - 13 characters.

Apperciate the help on this but it will take longer to figure this out than it will be to seperate by hand. I will just do it the old fashion way.

Also you dont have to be rude when replying to my questions!!

[NeoPa]

@slenish
I wasn't aware I was being. If you'd like to be more specific I could consider what you're saying.

Frankly, I thought I was being quite patient considering the number of times I seemed to need to repeat the same points. Consideration would be shown by replying to points raised by someone trying to help you. I'm quite used to a much lower level of behaviour from the general public though, than I would hold myself to, so I try to show patience.

If you think that being in a position where I need to point out again that you haven't responded to my request for some information that is consistent is somehow an indication that I lack manners, then clearly there is a large gulf between our different understandings of what determines considerate or polite behaviour.

Ultimately, there is no rule that you must reply in any way when requested for information, but to fail to do so is not likely to encourage anyone to want to volunteer their spare time trying to help you. If you want to make that my fault then I'm ok with that, but it won't get you very far ultimately.

[slenish]

It was not the comment of you wanting more information it was the comment of you saying

"This may not be answered here. New questions require separate threads. On the plus side, that is quite easy for you to do.

I find that rude on the basis as it comes off as you saying hey well at least you are smart enough to do this!

I tried to provide as much info as I could. Im not sure what kind of information you are looking for?? I thought I was pretty straight forward. I was going to post a screen shot but it does not look like I can. At this point it seems more trouble than its worth.

So im moving on to my other question in a new post

[TheSmileyCoder]

Hi Sleenish

There are several quite good and easy ways to do this. The problem is for us to decide, we need "high quality" test samples. The language of programming is so specific that when we write it we need to be so as well. What NeoPA (and myself) gets frustrated about is asking the same question to the Original Poster, several times over. Sometimes we end up doing this 5 times per forum visit, and the frustration builds up, and happens to get released on the 5th poster (in this case you).

Please provide 5 records exactly as they stand in your table.

Your example for instance,
[Name 1 Frank] (info)
When I look at that, I know it would be quite easy to make the split if the square brackets [] and the () are truly there. But im unsure as to whether they are truly part of the data, or if its something you added, trying to make your example more clear.

[NeoPa]

@slenish
In that case you can ignore my earlier comments. I can just assure you that my intention when writing that was not to sound patronising. You'd be surprised at the variety of posters we get on here. Some are so unsure of themselves, and so not at home when posting (as it all feels so foreign to them), that they really do post in the same thread because they think just starting a new thread is complicated. I don't wish to bore you with all the reasons why this makes it harder to work with for both the OP (You in this case) and the experts alike, but it's important for all the moderators (I'm also a moderator even though I'm an administrator) to ensure this is fixed when it occurs.

It's obviously easier to guess how someone will react after they've been posting a while, and easier to slip up when dealing with newer members. Let me just say that the wording there was an attempt to be friendly (as a moderator it's very easy to ruffle feathers anyway), rather than the reverse, and absolutely no offense was intended.

[slenish]

Neo Pa,

Everything is cool. I appologize for taking what you were saying the wrong way. I will also work toward trying to make sure I write out my questions better and word things better so they come across clearer. I do apperciate your help in all the questions I have posted thus far. I know you have been there on a few of them and I do apperciate you taking time out or you day to be there for people such as myself. I hope we can just put this behind us and keep moving forward on a positive beat.

See you in the next post

slen

TheSmileyOne about the [] brackets I put around my example. Sorry about that they were not supposed to be part of the wording I was just trying to use those symbolize a column. Is there another way I should be doing this to make it more clear?

Thanks

[TheSmileyCoder]

There is nothing wrong with brackets as long as you specify they are not part of the field data, they are used as delimiters.
Post a few lines of example data, but real data (if its social security numbers or something just switch out some of the numbers so its "faked" numbers, but keep the structure intact.
Column 1
[Name 1 Frank] (info)

The best way for us to determine a split, is by looking at real data. Again when I look at your example, I dont actually know whether Name is actually written in each row, or if you have replaced some parts of it. What is the (info) part for instance? is that also in column 1? Is it a very long string of stuff, or is it something simple. If you use the [code][/code] you can also make it stand nice and clear (with a little work).
If your columns have real names, it would also be a good idea to say so.

Example:

Column 1           Column 2
Name 1 Frank       Other info
Name 1 Frank       Other info
Name 1 Frank       Other info
Name 1 Frank       Other info
Name 2 Alice       Other info
Name 2 Alice       Other info
Name 2 Alice       Other info

If I knew this is what the data looks like, I would simply split on the second space, take me 30 seconds to write a split function for that. Instead we have used long time now asking back and forth... :P
You could even do a screenshot of your table, and poste that. An image speaks more then a thousand words! (Unless its VBA of course :P)

[NeoPa]

Everything cool indeed Slenish, though there's really no need to apologise. The medium that we work in is quite prone to misunderstandings (I suffered from a similar issue myself earlier as you saw). I can hardly blame you for misunderstanding me when I've already misunderstood you.

Moving on.

Smiley is clearly going through the same process that I am, looking for a reliable set of logic to implement. Neither of us has a problem implementing the logic once it's determined exactly what that is, and he, like I, feels we can make a pretty good stab of determining it for you if only we could see some live data. If you post that for us I expect some more helpful answer will be forthcoming shortly.

It's fine if you no longer wish to proceed (as stated earlier), but if you would like to then we both feel we're a simple step short of a productive result (At least if not that, we will be in a position to explain that the data doesn't lend itself to this process and human intervention would be required).

[topher23]

Wow, been a while since I've been on here. NeoPa, I did see Smiley's Split method, but since there were multiple spaces as delimiters, that method was no good. This is why I suggested using InstrRev to find the last instance of a space and use that as the delimiting factor. As my post showed, that method will work if you need to split the last word of data off and only the last word.

[NeoPa]

No worries Topher. If Slenish still requires this (which is now doubtful) we can probably catch it, but we'd need the example data first as the specification, as it stands, is too ambiguous. Most times incorrect answers come in, they are more a result of the question being unclear than a lack of understanding of the poster. There are exceptions, but usually it's in the complexity, or ambiguity, of the question. They're mostly caught one way or another too, so don't worry if sometimes you notice an answer that doesn't quite match.