Thread: How To Separate Text And Numbers From A String

1. How To Separate Text And Numbers From A String

Hi All,

lately I get Stock-Data from various sources by E-mail in the following format:
GRAPHITE ONE RESOURCES0,051+6,25 % - MOLYCORP0,365+18,89 % - GRAPHENE NANOCHEM0,472+0,64 % and so on.

Then I import that data into Excel and would like to have the format as shown in my attached Sample.

Please note that there should be a Blank before the Plus-sign.

Thank you very much in advance for your time and help.

Friendly regards,

Wolfgang

2. Hi WG,

It wasn't very clear what you wanted so I posted 2 solutions. Are you looking to do something like these?

wg4.png

Solution 1 in a standard module:
Code:
```Public Sub SplitStock1()
For H = 2 To 21
t = Split(Cells(H, 1), ",")
s = Split(Cells(H, 2), ",")
Cells(H, 4) = Left(t(0), Len(t(0)) - 1)
Cells(H, 6) = Left(s(0), Len(s(0)) - 1)
t(1) = "0," & t(1)
s(1) = "0," & s(1)
u = Replace(t(1), "+", " +", 1, 1)
v = Replace(s(1), "-", " -", 1, 1)
Cells(H, 5) = u & "," & t(2)
Cells(H, 7) = v & "," & s(2)
Next H
End Sub```
If solution 1 is what you need, I will condense the drafted code

Solution2 in a standard module:
Code:
```Public Sub SplitStock()
Row = 24
s = Split(Cells(24, 1), "-")
For I = 0 To UBound(s)
t = Split(s(I), ",")
Cells(Row, 4) = Left(t(J), Len(t(J)) - 1)
t(1) = "0," & t(1)
u = Replace(t(1), "+", " +", 1, 1)
Cells(Row, 5) = u & "," & t(2)
Row = Row + 1
Next I
End Sub```
If you could post a workbook with one sheet (sheet1) of your raw imported data and Sheet2 of how that exact data should result, that would enable us to code exactly what you need. As of now, it is a bit unclear how many stocks will be in the line. Your sample line in your post has 3 stocks in the line separated by hyphens whereas your sample spreadsheet doesn't match.

HTH,
Maud

3. The Following User Says Thank You to Maudibe For This Useful Post:

wolfgang (2015-06-06)

4. Hi Maude,

both solutions are just fine and I thank you very much for your kind help.

The data in my sample are the actual data but their order differ every minute.

At the end of each day I get this summary by E-mail.

Again, thank you VERY MUCH and have a nice weekend.

All the best,

Wolfgang

5. WG,

If you need help integrating it into your workbook, let me know.

Maud

6. Hi Maude,

I really appreciate your kind offer but the E-mail data are just for info to summarize the daily actions.

Friendly regards,

Wolfgang

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•