Sure.....

=TRIM(MID(A1,FIND("<",A1)+1,255))*1

The number you want to extract is at the end of the string:

.......MATERIAL@1< 813,936.29

Since the "<" character is a unique character and is the last character before the number all we need to do is find that character and extract everything to the right of that character. FIND("<",A1)+1 finds the position of the "<" character and then adds 1. This tells the MID function that that is the starting point of the string we want to extract. 255 is the number of characters that we want to extract. This is just an arbitrary number that is large enough to ensure that we extract all the remaining text to the right of the starting point. So, the string that has been extracted to this point looks like this:

=TRIM( 813,936.29)*1

We use the TRIM function to strip out any leading or trailing spaces so that now the string looks like this:

813,936.29

The MID function returns TEXT as its result so the number at this point is TEXT. We use the *1 to coerce the TEXT number into a numeric number so we can use it in other calculations.

Biff
"SSJ" wrote in message ...
Biff,

I was trying to understand the formula. Can you please explain the addition of 1255 & multiplication of 1 in your formula. What is it doing?

Thanks
SJ
"Biff" wrote in message ...
Hmmm.....

I just noticed something:

......MATERIAL@1< 813,936.29

The number is after the "<" character. If this is the same for all entries then it's even easier:

=TRIM(MID(A1,FIND("<",A1)+1,255))*1

Biff
"Biff" wrote in message ...
As long as the "whitespaces" are in fact standard spaces:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1

Biff
"SSJ" wrote in message ...
Hello!

Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ