By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

text to column

P: n/a
Hi, I have table wich looks like that

id detail
1 value1=15,value2=345,value3=2
2 value1=1523,value2=32,value3=2322
3 value1=2,value2=45,value3=34

How can I change it to this:

id value1 value2 value3
1 15 345 2
2 1523 32 2322
3 2 45 34

Thank you in advance
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
bobo previously wrote:
Hi, I have table wich looks like that

id detail
1 value1=15,value2=345,value3=2
2 value1=1523,value2=32,value3=2322
3 value1=2,value2=45,value3=34

How can I change it to this:

id value1 value2 value3
1 15 345 2
2 1523 32 2322
3 2 45 34

Thank you in advance


What's the difference? They look the same to me.
Nov 12 '05 #2

P: n/a
"bobo" <bo**@vip.bg> wrote in message
news:c1**************************@posting.google.c om...
Hi, I have table wich looks like that

id detail
1 value1=15,value2=345,value3=2
2 value1=1523,value2=32,value3=2322
3 value1=2,value2=45,value3=34

How can I change it to this:

id value1 value2 value3
1 15 345 2
2 1523 32 2322
3 2 45 34

Thank you in advance


The file actually contains the text "value1=", etc?

If this is a quick conversion, paste it into Excel and use Data>Text To
Columns and put the "=" in the "other" box and also select space. Choose to
treat consecutive delimiters as one.

If you are looking for something more automated, then let us know if you can
do anything with VBA.
Fletcher
Nov 12 '05 #3

P: n/a


Hi again.
No this is not a quick conversion.
I'm familiar with VB if you give me an idea I think will help...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a


an*******@devdex.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

P: n/a
Fletcher Arnold previously wrote:
The file actually contains the text "value1=", etc?

This function will enable you to pull out any value from the example you
give.
Public Function SplitUp(iText, pos)

Dim x() As String
x = Split(iText, ",")

SplitUp = Val(Mid(Trim(x(pos - 1)), 8, 10))

End Function
Usage:
in a query: update to :
splitup([Details],1) will give the first value
splitup([Details],2) will give the second value etc.

It will work up to value9, provided values are separated with a comma.

Peter Russell
Nov 12 '05 #6

P: n/a

"borislav simeonov" <bo**@vip.bg> wrote in message
news:40*********************@news.frii.net...


Hi again.
No this is not a quick conversion.
I'm familiar with VB if you give me an idea I think will help...

Did you get Peter Russell's idea to work?

Fletcher
Nov 12 '05 #7

P: n/a
ru***@127.0.0.1 (Peter Russell) wrote in message news:<me***********************@russellscott.btint ernet.com>...
bobo previously wrote:
Hi, I have table wich looks like that

id detail
1 value1=15,value2=345,value3=2
2 value1=1523,value2=32,value3=2322
3 value1=2,value2=45,value3=34

How can I change it to this:

id value1 value2 value3
1 15 345 2
2 1523 32 2322
3 2 45 34

Thank you in advance


What's the difference? They look the same to me.


Perhaps bobo meant that he has one table with 2 columns (Id, Detail)
and wants to transform it into a new table with 4 columns (Id, Value1,
Value2, Value3).

If this was the question, then
1. Use a recordset to retrieve rows from the first table
2. While no more rows in recordset
a. use string parsing functions (Mid, Left, InStr, etc)
to parse each value after the "=" sign and before the comma
","
b. insert into the new table using the values just parsed
3. close recordset

Refer to the On-line help documentation for how to use the string
parsing functions.

Hope this was helpful.
Steve Cummings
Nov 12 '05 #8

P: n/a


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #9

P: n/a


Yes this work just fine
Thank you

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.