Breaking the string into Text / Number

[Amerigo]

So for the example below - everyrhing starting with 1 to go to go to the separate column


Is there an easy way ?




Weighted Average Duration 1.07 4.99Weighted Average Maturity 1.20 12.57Weighted Average Yield to Worst* 1.96 3.30

[ajetrumpet]

hmm...this post is not comprehendable.

what do you want to do?

[Amerigo]

hmm...this post is not comprehendable.

what do you want to do?

hey -
so I have 1 column containing text followed by numbers (exam below)
I'd like to separate text/number

Weighted Average Duration 1.07 4.99
Weighted Average Maturity 1.20 12.57
Weighted Average Yield to Worst* 1.96 3.30

[Bob Fitz]

Hi
Yes it is possible but to give you help on how to do it I need a little more information on exactly what you want to do. For example:

1 Where and how are you displaying the column? (Table, Query DataSheet Form)

2 You want to seperate the numbers and then do what? (Show them in a second column? )

3 Do yoy want to save the seperation in your tables?

[Amerigo]

hey Bob - thanks for replying
yes data is in one column currently, I would like to show the numbers in the second column (in a query)

sorry, don't understand the 3rd column

[Bob Fitz]

Hi
Can you post the SQL of this query?

[Amerigo]

Hi
Can you post the SQL of this query?

There's no query at the present - I am planning t ohave a query based on that table that has 1 column

eewettr5678
tfhujftrjytfc6778
tjyjyjg7878%
ehhtibkmbmfmtlklkrd2132

[Bob Fitz]

OK. Can you tell us the name of the table and the name of the one field?

[Amerigo]

OK. Can you tell us the name of the table and the name of the one field?

Tbl1 , Field1 is fine )
I'll update once i'll what it was in the office

[Bob Fitz]

Hi
This should give you the results that you require:

SELECT Trim(Left([Field1],Len([Field1])-10)) AS TxtFld, Trim(Right([Field1],10)) AS NumFld
FROM Table1;

[Amerigo]

Hi
This should give you the results that you require:

SELECT Trim(Left([Field1],Len([Field1])-10)) AS TxtFld, Trim(Right([Field1],10)) AS NumFld
FROM Table1;

wow, almost there
except it's bringing first number
Field1TxtFldNumFldNO VALUE 0.01% 0.00%NO VALUE 0.01% 0.00%Swap 0.00% 0.00%Swap 0.00% 0.00%EMD 0.04% 1.38%EMD 0.04% 1.38%Agency 0.00% 0.00%Agency 0.00% 0.00%Basic Industry 0.00% 0.05%Basic Industry 0.00% 0.05%Capital Goods 0.00% 0.03%Capital Goods 0.00% 0.03%Communications 0.02% 0.05%Communications 0.02% 0.05%

[Bob Fitz]

Sorry, but I don't understand your reply.
I tested the proposed solution with the data you posted in your second post:
Weighted Average Duration 1.07 4.99
Weighted Average Maturity 1.20 12.57
Weighted Average Yield to Worst* 1.96 3.30

This is the result:

Query1TxtFldNumFldWeighted Average Duration1.07 4.99Weighted Average Maturity1.20 12.57Weighted Average Yield to Worst*1.96 3.30

Where does:
Field1TxtFldNumFldNO VALUE 0.01% 0.00%NO VALUE 0.01% 0.00%Swap 0.00% 0.00%Swap 0.00% 0.00%EMD 0.04% 1.38%EMD 0.04% 1.38%Agency 0.00% 0.00%Agency 0.00% 0.00%Basic Industry 0.00% 0.05%Basic Industry 0.00% 0.05%Capital Goods 0.00% 0.03%Capital Goods 0.00% 0.03%Communications 0.02% 0.05%Communications 0.02% 0.05%
Come from?

[Amerigo]

silly me, i just had to change it to 11

[Bob Fitz]

We all make simple mistakes, but does it solve your problem?

[Amerigo]

Sorry, but I don't understand your reply.
I tested the proposed solution with the data you posted in your second post:
Weighted Average Duration 1.07 4.99
Weighted Average Maturity 1.20 12.57
Weighted Average Yield to Worst* 1.96 3.30

This is the result:

Query1TxtFldNumFldWeighted Average Duration1.07 4.99Weighted Average Maturity1.20 12.57Weighted Average Yield to Worst*1.96 3.30

Where does:
Field1TxtFldNumFldNO VALUE 0.01% 0.00%NO VALUE 0.01% 0.00%Swap 0.00% 0.00%Swap 0.00% 0.00%EMD 0.04% 1.38%EMD 0.04% 1.38%Agency 0.00% 0.00%Agency 0.00% 0.00%Basic Industry 0.00% 0.05%Basic Industry 0.00% 0.05%Capital Goods 0.00% 0.03%Capital Goods 0.00% 0.03%Communications 0.02% 0.05%Communications 0.02% 0.05%
Come from?

I guess at some points the number at the end becomes longer; i'll look through on monday, but i can figure an IIF statement for that

thnaks a lot !!!!!!!!