Parsing Number from Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?
 
Try something like this:

=SUM(MID(LEFT(AL19,SEARCH("
B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH("
B/O",AL50)-1),SEARCH("GBP",AL50)+3,255))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?
 
Yes! I'm sorry....I didn't pay close enough attention at first.
You need the test applied to every cell in a range, right?

Try something like this ARRAY FORMULA:
=SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A55)),MID(LEFT(A2:A55,SEARCH("
B/O",A2:A55)-1),SEARCH("GBP",A2:A55)+3,255)))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Adjust the range references to suit your situation. (I used A2:A55)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
No need for me to be lazy, right?

Here's the formula, referencing AL2:AL300

=SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2:AL300,SEARCH("
B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255)))

( remember to use [ctrl]+[shift]+[enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
For some reason I got a #VALUE! response when I do the shift-cntrl-enter
Maybe I should be more specific....
Here's the exact wording that's in one of my AL cells.

REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN
EXPRESS LTD

Would this alter the formula?

Thanks again!

Here's the

Ron Coderre said:
No need for me to be lazy, right?

Here's the formula, referencing AL2:AL300

=SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2:AL300,SEARCH("
B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255)))

( remember to use [ctrl]+[shift]+[enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Brian said:
Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?
Click to expand...
Click to expand...
 
OK, Brian.....This is the latest in a series of final formulas :)

(ARRAY FORMULA)
=SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A300,SEARCH("
REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Brian said:
For some reason I got a #VALUE! response when I do the shift-cntrl-enter
Maybe I should be more specific....
Here's the exact wording that's in one of my AL cells.

REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN
EXPRESS LTD

Would this alter the formula?

Thanks again!

Here's the

Ron Coderre said:
No need for me to be lazy, right?

Here's the formula, referencing AL2:AL300

=SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2:AL300,SEARCH("
B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255)))

( remember to use [ctrl]+[shift]+[enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Brian said:
Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?

:

Try something like this:

=SUM(MID(LEFT(AL19,SEARCH("
B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH("
B/O",AL50)-1),SEARCH("GBP",AL50)+3,255))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Ron....but still no luck.
I tried the formula listed below (changed the A2:A300 to AL2:AL300) and
still get the #VALUE! response.
Some other rows have data in column AL....but I don't need any of these if
they don't have that GBP string in them.
Would that matter?

Ron Coderre said:
OK, Brian.....This is the latest in a series of final formulas :)

(ARRAY FORMULA)
=SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A300,SEARCH("
REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Brian said:
For some reason I got a #VALUE! response when I do the shift-cntrl-enter
Maybe I should be more specific....
Here's the exact wording that's in one of my AL cells.

REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN
EXPRESS LTD

Would this alter the formula?

Thanks again!

Here's the

Ron Coderre said:
No need for me to be lazy, right?

Here's the formula, referencing AL2:AL300

=SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2:AL300,SEARCH("
B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255)))

( remember to use [ctrl]+[shift]+[enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?

:

Try something like this:

=SUM(MID(LEFT(AL19,SEARCH("
B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH("
B/O",AL50)-1),SEARCH("GBP",AL50)+3,255))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Do other cells in Col_AL contain "GBP", but no "REFNO"
or no amount between "GBP" and "REFNO"?


***********
Regards,
Ron

XL2002, WinXP


Brian said:
Thanks again Ron....but still no luck.
I tried the formula listed below (changed the A2:A300 to AL2:AL300) and
still get the #VALUE! response.
Some other rows have data in column AL....but I don't need any of these if
they don't have that GBP string in them.
Would that matter?

Ron Coderre said:
OK, Brian.....This is the latest in a series of final formulas :)

(ARRAY FORMULA)
=SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A300,SEARCH("
REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Brian said:
For some reason I got a #VALUE! response when I do the shift-cntrl-enter
Maybe I should be more specific....
Here's the exact wording that's in one of my AL cells.

REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN
EXPRESS LTD

Would this alter the formula?

Thanks again!

Here's the

:

No need for me to be lazy, right?

Here's the formula, referencing AL2:AL300

=SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2:AL300,SEARCH("
B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255)))

( remember to use [ctrl]+[shift]+[enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?

:

Try something like this:

=SUM(MID(LEFT(AL19,SEARCH("
B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH("
B/O",AL50)-1),SEARCH("GBP",AL50)+3,255))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
No....most of the other rows that have data don't have GBP in them.
Most of them are blank.....or have some other words....but not GBP

Ron Coderre said:
Do other cells in Col_AL contain "GBP", but no "REFNO"
or no amount between "GBP" and "REFNO"?


***********
Regards,
Ron

XL2002, WinXP


Brian said:
Thanks again Ron....but still no luck.
I tried the formula listed below (changed the A2:A300 to AL2:AL300) and
still get the #VALUE! response.
Some other rows have data in column AL....but I don't need any of these if
they don't have that GBP string in them.
Would that matter?

Ron Coderre said:
OK, Brian.....This is the latest in a series of final formulas :)

(ARRAY FORMULA)
=SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A300,SEARCH("
REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

For some reason I got a #VALUE! response when I do the shift-cntrl-enter
Maybe I should be more specific....
Here's the exact wording that's in one of my AL cells.

REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN
EXPRESS LTD

Would this alter the formula?

Thanks again!

Here's the

:

No need for me to be lazy, right?

Here's the formula, referencing AL2:AL300

=SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2:AL300,SEARCH("
B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255)))

( remember to use [ctrl]+[shift]+[enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?

:

Try something like this:

=SUM(MID(LEFT(AL19,SEARCH("
B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH("
B/O",AL50)-1),SEARCH("GBP",AL50)+3,255))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
The issue probably lies in the data. So far, I only get the #VALUE! error if
the characters between "GBP" and " REFNO" contain letters or do not resolve
to a valid number:

Examples:
These work....
GBP 100.00 REFNO
GBP 00100.00 REFNO
GBP100.00 REFNO

These don't....
GBP a100.00 REFNO........"a"
GBP 100.00REFNO...........no space before REFNO
GBP 10.0.0.0 REFNO...........extra decimal points

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Brian said:
No....most of the other rows that have data don't have GBP in them.
Most of them are blank.....or have some other words....but not GBP

Ron Coderre said:
Do other cells in Col_AL contain "GBP", but no "REFNO"
or no amount between "GBP" and "REFNO"?


***********
Regards,
Ron

XL2002, WinXP


Brian said:
Thanks again Ron....but still no luck.
I tried the formula listed below (changed the A2:A300 to AL2:AL300) and
still get the #VALUE! response.
Some other rows have data in column AL....but I don't need any of these if
they don't have that GBP string in them.
Would that matter?

:

OK, Brian.....This is the latest in a series of final formulas :)

(ARRAY FORMULA)
=SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A300,SEARCH("
REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

For some reason I got a #VALUE! response when I do the shift-cntrl-enter
Maybe I should be more specific....
Here's the exact wording that's in one of my AL cells.

REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN
EXPRESS LTD

Would this alter the formula?

Thanks again!

Here's the

:

No need for me to be lazy, right?

Here's the formula, referencing AL2:AL300

=SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2:AL300,SEARCH("
B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255)))

( remember to use [ctrl]+[shift]+[enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?

:

Try something like this:

=SUM(MID(LEFT(AL19,SEARCH("
B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH("
B/O",AL50)-1),SEARCH("GBP",AL50)+3,255))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
I'll double check the other lines that have GBP in them....
I thought they were all formatted the same way (ie../BNF/GBP391.38
REFNO)....but maybe I'm missing something.

Thanks again for your help.

Ron Coderre said:
The issue probably lies in the data. So far, I only get the #VALUE! error if
the characters between "GBP" and " REFNO" contain letters or do not resolve
to a valid number:

Examples:
These work....
GBP 100.00 REFNO
GBP 00100.00 REFNO
GBP100.00 REFNO

These don't....
GBP a100.00 REFNO........"a"
GBP 100.00REFNO...........no space before REFNO
GBP 10.0.0.0 REFNO...........extra decimal points

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Brian said:
No....most of the other rows that have data don't have GBP in them.
Most of them are blank.....or have some other words....but not GBP

Ron Coderre said:
Do other cells in Col_AL contain "GBP", but no "REFNO"
or no amount between "GBP" and "REFNO"?


***********
Regards,
Ron

XL2002, WinXP


:

Thanks again Ron....but still no luck.
I tried the formula listed below (changed the A2:A300 to AL2:AL300) and
still get the #VALUE! response.
Some other rows have data in column AL....but I don't need any of these if
they don't have that GBP string in them.
Would that matter?

:

OK, Brian.....This is the latest in a series of final formulas :)

(ARRAY FORMULA)
=SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A300,SEARCH("
REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255)))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

For some reason I got a #VALUE! response when I do the shift-cntrl-enter
Maybe I should be more specific....
Here's the exact wording that's in one of my AL cells.

REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN
EXPRESS LTD

Would this alter the formula?

Thanks again!

Here's the

:

No need for me to be lazy, right?

Here's the formula, referencing AL2:AL300

=SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2:AL300,SEARCH("
B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255)))

( remember to use [ctrl]+[shift]+[enter] )

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Thanks Ron....but is there a way I can put in a range of cells to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?

:

Try something like this:

=SUM(MID(LEFT(AL19,SEARCH("
B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH("
B/O",AL50)-1),SEARCH("GBP",AL50)+3,255))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?
Click to expand...

Try this:

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

It can be embedded in the workbook if that is an issue.

Then use this **array** formula (enter with <ctrl><shift><enter>)

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP).*?(?=\s|$)"))

where "rng" is the range that contains your strings.
--ron
 
Try this:

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

It can be embedded in the workbook if that is an issue.

Then use this **array** formula (enter with <ctrl><shift><enter>)

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP).*?(?=\s|$)"))

where "rng" is the range that contains your strings.
--ron
Click to expand...

This might be more foolproof:

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+"))


--ron
 
Ron Rosenfeld said:
This might be more foolproof:

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+"))
Click to expand...

This fails with Ron Coderre's pathological samples,

These work....
GBP 100.00 REFNO
GBP 00100.00 REFNO
GBP100.00 REFNO

These don't....
GBP a100.00 REFNO........"a"
GBP 100.00REFNO...........no space before REFNO
GBP 10.0.0.0 REFNO...........extra decimal points

IMO, you need to use REGEX.SUBSTITUTE to do this.

REGEX.SUBSTITUTE(x,".*GBP[^-0-9.]*(-?\d*\.?\d*)[^0-9]?.*","[1]")
 
Hmmm..."pathological"
Interesting choice of word, Dick

Per Merriam-Webster:
"being such to a degree that is extreme, excessive, or markedly abnormal"

Exactly what I was trying to demonstrate....that perhaps the structure of
the text wasn't as rigid as we might have thought, ultimately requiring a
much more arcane solution....as you so aptly provided.

***********
Regards,
Ron

XL2002, WinXP


Harlan Grove said:
Ron Rosenfeld said:
This might be more foolproof:

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+"))
Click to expand...

This fails with Ron Coderre's pathological samples,

These work....
GBP 100.00 REFNO
GBP 00100.00 REFNO
GBP100.00 REFNO

These don't....
GBP a100.00 REFNO........"a"
GBP 100.00REFNO...........no space before REFNO
GBP 10.0.0.0 REFNO...........extra decimal points

IMO, you need to use REGEX.SUBSTITUTE to do this.

REGEX.SUBSTITUTE(x,".*GBP[^-0-9.]*(-?\d*\.?\d*)[^0-9]?.*","[1]")
Click to expand...
 
Ron Rosenfeld said:
This might be more foolproof:

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+"))
Click to expand...

This fails with Ron Coderre's pathological samples,

These work....
GBP 100.00 REFNO
GBP 00100.00 REFNO
GBP100.00 REFNO

These don't....
GBP a100.00 REFNO........"a"
GBP 100.00REFNO...........no space before REFNO
GBP 10.0.0.0 REFNO...........extra decimal points

IMO, you need to use REGEX.SUBSTITUTE to do this.

REGEX.SUBSTITUTE(x,".*GBP[^-0-9.]*(-?\d*\.?\d*)[^0-9]?.*","[1]")
Click to expand...

You're correct.

As written, the REGEX.MID (my 2nd one) would handle the issue of no space
before REFNO.

However, I did make the assumption that there was no space between GPB and the
value to be extracted; and that that value did not contain multiple decimals,
and that it was a positive value.

It would be easy to add in the option for an optional space between GBP and the
value:

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\s\d\.]+"))

and one could even handle the possibility of signed numbers and multiple
decimals by using a more general notation for floating point numbers:

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)\s*[-+]?\d*\.?\d+"))

But to handle the possibility of extraneous characters between GBP and the
number, like the "a" above, I would absolutely agree that the REGEX.SUBSTITUTE
would be better.


--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Question 2
Counting days and text in cells 3
How to create a crosstab in Excel 1
Edit File Information 3
Copy Data from One Spreadsheet to Another ?????? 5
Inserting Text Makes Numbers Disappear! 5
Exrtacting text from a string 2
Rearranging text data. 4

Back
Top