Can I convert text to columns in a query?

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Can I convert text to columns in a query? I know I can do it in Excel, but it
would be simpler if I knew a way to do it in Access. Here's the field I'm
trying to separate:

Field 1:
2.00% @ 24 3.00% @ 24 4.00% @ 48

I would like to separate that one field into six new fields, like this:

Field 1: Field 2: Field 3: Field 4: Field 5: Field 6:
2.00% 24 3.00% 24 4.00% 48

Is this possible?
 
Can I convert text to columns in a query? I know I can do it in Excel, but it
would be simpler if I knew a way to do it in Access. Here's the field I'm
trying to separate:

Field 1:
2.00% @ 24 3.00% @ 24 4.00% @ 48

I would like to separate that one field into six new fields, like this:

Field 1:        Field 2:        Field 3:        Field 4:        Field 5:        Field 6:
2.00%   24      3.00%   24      4.00%   48

Is this possible?
Click to expand...

are these separate columns? Or is everything in one long string? (If
it is, someone deserves a beating.) You could take something like
this:

Public Function Shplit(strInput As String) As Variant
'2.00% @ 24 3.00% @ 24 4.00% @ 48
Dim varOutput As Variant
Dim intPos As Integer

Shplit = Replace(strInput, " @ ", " ")
varOutput = Split(Shplit, " ")

For intPos = LBound(varOutput) To UBound(varOutput)
Debug.Print intPos, varOutput(intPos)
Next intPos

End Function

?Shplit("2.00% @ 24 3.00% @ 24 4.00% @ 48")
0 2.00%
1 24
2 3.00%
3 24
4 4.00%
5 48

Then you would have to write this stuff back to a table somewhere...

Maybe Vanderghast can do this in a single query, but I'm not that
good...
 
Piet Linden said:
are these separate columns? Or is everything in one long string? (If
it is, someone deserves a beating.) You could take something like
this:

Public Function Shplit(strInput As String) As Variant
'2.00% @ 24 3.00% @ 24 4.00% @ 48
Dim varOutput As Variant
Dim intPos As Integer

Shplit = Replace(strInput, " @ ", " ")
varOutput = Split(Shplit, " ")

For intPos = LBound(varOutput) To UBound(varOutput)
Debug.Print intPos, varOutput(intPos)
Next intPos

End Function

?Shplit("2.00% @ 24 3.00% @ 24 4.00% @ 48")
0 2.00%
1 24
2 3.00%
3 24
4 4.00%
5 48

Then you would have to write this stuff back to a table somewhere...

Maybe Vanderghast can do this in a single query, but I'm not that
good...
Click to expand...

Thank you! That solves my problem. Yes, the table was set up like that...I
have no idea why.
 
Back
Top