Finding a charcter in a text string

[Guest]

I download weekly information off the web that has been formatted as text
strings. I have set this up as a web query, so all I have now to do is
refresh the query each week to get the updated information. I also know that
some of you will just say use text to column function that is in Excel, using
the above method; I do not see that as an option, so have decided to just go
with a formula or formulas to speed this process up.

In most cases each line string is no longer then 90 characters in length.
This site uses a two character number that is associated with each salesman.
Therefore the number and the name are usually no longer then 12 characters in
length. The next set of data in this string is the number of contacts each
salesman makes. Following that is the numbers of sales. For this example I
will use three products. So a line can have something like this: 22 Mark S
6 0 0 0. So what this is telling us is that Mark S has made six contacts,
and has zero sales of all three products. Now for another salesman they
could have something like this: 58 John B 61 10 5 15. Now this is telling us
that John has made sixty one contacts, and has sold ten of product X, and
sold five of product Z, and sold fifteen of product Q.

Presenting I am just keying this information in by hand. What I want to do
is to have a formula or set of formulas that will take this information and
paste it on another spreadsheet in the appropriate column for that salesman
and product.

Can somebody help me with this formula?

Steve

 

[RagDyeR]

Since you don't want to use TTC, another approach can be text formulas with
a Vlookup.

Using text formulas, you'll need to identify the different fields
accurately, probably having to count the spaces in the original imported
data.

Will need some *exact* examples of that data.
For example, is there a space between the last name and the initial?
Is there a single space between each of the fields?

Care to post some exact examples?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I download weekly information off the web that has been formatted as text
strings. I have set this up as a web query, so all I have now to do is
refresh the query each week to get the updated information. I also know that
some of you will just say use text to column function that is in Excel,
using
the above method; I do not see that as an option, so have decided to just go
with a formula or formulas to speed this process up.

In most cases each line string is no longer then 90 characters in length.
This site uses a two character number that is associated with each salesman.
Therefore the number and the name are usually no longer then 12 characters
in
length. The next set of data in this string is the number of contacts each
salesman makes. Following that is the numbers of sales. For this example I
will use three products. So a line can have something like this: 22 Mark S
6 0 0 0. So what this is telling us is that Mark S has made six contacts,
and has zero sales of all three products. Now for another salesman they
could have something like this: 58 John B 61 10 5 15. Now this is telling
us
that John has made sixty one contacts, and has sold ten of product X, and
sold five of product Z, and sold fifteen of product Q.

Presenting I am just keying this information in by hand. What I want to do
is to have a formula or set of formulas that will take this information and
paste it on another spreadsheet in the appropriate column for that salesman
and product.

Can somebody help me with this formula?

Steve

 

[Guest]

After looking at the text column function one more time, I see now how it
will work for me. Thanks for your reply.

 

[Ragdyer]

I believe you'll see that you made the right decision.

Post back if you have any questions pertaining to TTC.