Text to Columns in access ???????

unc2plo

Board Regular
Joined
Mar 18, 2002
Messages
148
I need help duplicating "text to columns" in access.

I have a database field that is 6 characters log. I need the field broken down into 3 columns depending on where the hyphen is.

I tried using Left & Right, but the problem is that in some cases the entire 6 characters are used up, and not in others, but the extra character space is still there.

Example:
Shifts
12-2-1
3-1-2

Is there some sort of VBA code that can do the breakouts based on teh hyphen vs character count?


Thanks,
David
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Just use

For the first one:

Left([FieldName], Instr(1, [FieldName], "-") -1)


and for the second:

Mid([FieldNameHere], InStr(1, [FieldNameHere], "-") + 1, Len([FieldNameHere]) - InStrRev([FieldNameHere], "-"))

And for the last

Right([FieldNameHere], Len([FieldNameHere]) - InStrRev([FieldNameHere], "-"))
 
Upvote 0
Put this function in a standard module and then call it as you wish:
Code:
Function SplitField(strValue As String, strDelimiter As String, intPartWanted As Integer) As String
    SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1)
End Function

In the query you can pass the value like:

Code:
SELECT SplitField([YourFieldNameHere],"-",1) AS TheLeft, SplitField([YourFieldNameHere],"-",2) AS TheMiddle, SplitField([YourFieldNameHere],"-",3) AS TheRight
FROM Table4;
 
Upvote 0
Bob,
Thanks for the quick response.

I am afraid I am not following you entirely though.

I added the VBA to a new module, but I am not sure how to add it to the query.

This is a field in a larger table, and I want to still tie it to other data.

Thanks,
David
 
Upvote 0
Just create new fields in your query in the QBE grid (the other example I posted used the actual SQL instead).

So, in the field area you type this for the left one

MyLeft:SplitField([YourFieldNameHere],"-",1)

and the middle

MyMiddle:SplitField([YourFieldNameHere],"-",2)

and the right one

MyRight:SplitField([YourFieldNameHere],"-",2)

and as long as you named the module something other than SplitField (you don't name the module the same name as a procedure inside of it, to make it less difficult to reference).

And that is it. Just remember to replace YourFieldNameHere with the actual name of your field which has the 12-2-1 in it.
 
Upvote 0
please can u help me to seperate the below i need only the text and plaese be informed that the data type is text

110502-مصاريف اشتراكات كهرباء و ماء و هاتف
12-الموجودات المتداولة
1201-الزبائن
i need to seperate the text from column in one criteria
 
Upvote 0
Just create new fields in your query in the QBE grid (the other example I posted used the actual SQL instead).

So, in the field area you type this for the left one

MyLeft:SplitField([YourFieldNameHere],"-",1)

and the middle

MyMiddle:SplitField([YourFieldNameHere],"-",2)

and the right one

MyRight:SplitField([YourFieldNameHere],"-",2)

and as long as you named the module something other than SplitField (you don't name the module the same name as a procedure inside of it, to make it less difficult to reference).

And that is it. Just remember to replace YourFieldNameHere with the actual name of your field which has the 12-2-1 in it.

Hi there!
I have a similar Problem with a text separated with ";". I tried to use your Formula (Thanks for sharing!!) but the system is complaining:

"There was an error compiling this function
The Visual Basic module contains a syntax error.
Check the code, and then recompile it."

I didn't change anything yet (was going to change my ";" for "-" to check if it works).
So I just created the Module (Called it sth different) and paste the "Formulas" on my Query. Does somebody now what could be wrong?
Thanks a lot for your time!
 
Upvote 0
1. I don't understand what you mean by "Called it sth different."
2. Pasted what in your query?

Ok sorry I explain it slowly. I changed my MS Office Access to English (I was working in German, don't know if that's the reason for the other error I was getting...)

1. I created a Module with your Code:

Function SplitField(strValue As String, strDelimiter As String, intPartWanted As Integer) As String
SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1)
End Function


I saved it and called it "M_Trennen"

2. I created a query (Q_Trennen) and (in Design view) added the field I want to separate [Text] + 3 further columns

Column1: [Text]
Column2: MyLeft:SplitField([Text];"-";1)
Column3: MyMiddle:SplitField([Text];"-";2)
Column4: MyRight: SplitField([Text];"-";2)

Note that I had to change "," with ";" because Access was complaining.

Now I get this error, which seems to appear for every line (63.000!!):

Run-time error '9'.
Subscript out of range.

And this result (I just copied first lines):

Text | My Left | MyMiddle | MyRight
A1; A1; #Error #Error
Q5 Q5 #Error #Error
Q5- Q5
X4-Usluga X4 Usluga Usluga
X7-Kosten 2015 X7 Kosten 2015 Kosten 2015

Do you know why I get the Run-Time error?
Can I easily replace the "-" for a ";"?
I could have up to 5 different codes separated with ";" can I do more Middleparts?
I am sorry for all the questions but I am trying for days to find a solution to that and I don't get it. I just did it with Excel until now but it's so annoying having to use Excel only for this.
Many thanks in advance!
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top