ExcelBanter - Text function

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Text function (https://www.excelbanter.com/excel-discussion-misc-queries/238943-text-function.html)

I have an extract from a long list below. I would like to strip out the
second number from each text. i.e example from the first two rows are 1000
and 3104 respectively. Any help would be greatly appreciated.

010114 Long K 1000 Basic Pay
042140 Balard S 3104 FLS AD Hrs
040557 Sanjay Conroy A 3001 Fee
042316 Christie Eron C 1006 Basic Arrs
020674 De Thang C 1104 Basic-FLS
040203 De La Conjo MF 1000 Basic Pay
020025 Van Wanjo E 1010 Lon All'ce
040471 Van Kot FWM 1102 Basic-FLC
040471 Van Kot FWM 1112 L.A. - FLC

One way

A1 = 010114 Long K 1000 Basic Pay

In B1 use the below formula

=--LEFT(MID(MID(A1,FIND("
",A1)+1,99),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(A 1,FIND("
",A1)+1,99)&"0123456789")),99),FIND(" ",MID(MID(A1,FIND("
",A1)+1,99),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(A 1,FIND("
",A1)+1,99)&"0123456789")),99)))

If this post helps click Yes
---------------
Jacob Skaria

"nc" wrote:

I have an extract from a long list below. I would like to strip out the
second number from each text. i.e example from the first two rows are 1000
and 3104 respectively. Any help would be greatly appreciated.

010114 Long K 1000 Basic Pay
042140 Balard S 3104 FLS AD Hrs
040557 Sanjay Conroy A 3001 Fee
042316 Christie Eron C 1006 Basic Arrs
020674 De Thang C 1104 Basic-FLS
040203 De La Conjo MF 1000 Basic Pay
020025 Van Wanjo E 1010 Lon All'ce
040471 Van Kot FWM 1102 Basic-FLC
040471 Van Kot FWM 1112 L.A. - FLC

OR

=--LEFT(MID(MID(A1,FIND(CHAR(32),A1)+1,99),MIN(SEARCH ({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND(CHAR(32),A1)+1, 99)&"0123456789")),99),FIND(CHAR(32),MID(MID(A1,FI ND(CHAR(32),A1)+1,99),MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},MID(A1,FIND(CHAR(32),A1)+1,99)&"0123456789")) ,99)))

If this post helps click Yes
---------------
Jacob Skaria

"nc" wrote:

I have an extract from a long list below. I would like to strip out the
second number from each text. i.e example from the first two rows are 1000
and 3104 respectively. Any help would be greatly appreciated.

010114 Long K 1000 Basic Pay
042140 Balard S 3104 FLS AD Hrs
040557 Sanjay Conroy A 3001 Fee
042316 Christie Eron C 1006 Basic Arrs
020674 De Thang C 1104 Basic-FLS
040203 De La Conjo MF 1000 Basic Pay
020025 Van Wanjo E 1010 Lon All'ce
040471 Van Kot FWM 1102 Basic-FLC
040471 Van Kot FWM 1112 L.A. - FLC

"nc" wrote:
I have an extract from a long list below.

If the first number is always the first 6 characters followed by at least
one character (e.g. space), and if the second number is always 4 characters,
then:

=--MID(A1, MIN(FIND({1,2,3,4,5,6,7,8,9,0}, A1&"1234567890", 8)), 4)

This returns a numeric result; format with a desired number format, if
necessary. Omit "--" if you want a text result; format with a desired
horizontal alignment, if necessary.

----- original message -----

"nc" wrote in message
...
I have an extract from a long list below. I would like to strip out the
second number from each text. i.e example from the first two rows are 1000
and 3104 respectively. Any help would be greatly appreciated.

010114 Long K 1000 Basic Pay
042140 Balard S 3104 FLS AD Hrs
040557 Sanjay Conroy A 3001 Fee
042316 Christie Eron C 1006 Basic Arrs
020674 De Thang C 1104 Basic-FLS
040203 De La Conjo MF 1000 Basic Pay
020025 Van Wanjo E 1010 Lon All'ce
040471 Van Kot FWM 1102 Basic-FLC
040471 Van Kot FWM 1112 L.A. - FLC

Hi Jacob

Thanks. Can you please explain what is the function of the double minus
sign? and what the function is doing.

"Jacob Skaria" wrote:

One way

A1 = 010114 Long K 1000 Basic Pay

In B1 use the below formula

=--LEFT(MID(MID(A1,FIND("
",A1)+1,99),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(A 1,FIND("
",A1)+1,99)&"0123456789")),99),FIND(" ",MID(MID(A1,FIND("
",A1)+1,99),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(A 1,FIND("
",A1)+1,99)&"0123456789")),99)))

If this post helps click Yes
---------------
Jacob Skaria

"nc" wrote:

I have an extract from a long list below. I would like to strip out the
second number from each text. i.e example from the first two rows are 1000
and 3104 respectively. Any help would be greatly appreciated.

010114 Long K 1000 Basic Pay
042140 Balard S 3104 FLS AD Hrs
040557 Sanjay Conroy A 3001 Fee
042316 Christie Eron C 1006 Basic Arrs
020674 De Thang C 1104 Basic-FLS
040203 De La Conjo MF 1000 Basic Pay
020025 Van Wanjo E 1010 Lon All'ce
040471 Van Kot FWM 1102 Basic-FLC
040471 Van Kot FWM 1112 L.A. - FLC

=SEARCH({0,1,2,3,4,5,6,7,8,9},G2,6)

I was trying to use this function to search the the following numbers and it
works.

=SEARCH({1000,3000},G2,6)

Why it does not work when I change it to the above when I have 3000 in the
text?

"Jacob Skaria" wrote:

OR

=--LEFT(MID(MID(A1,FIND(CHAR(32),A1)+1,99),MIN(SEARCH ({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND(CHAR(32),A1)+1, 99)&"0123456789")),99),FIND(CHAR(32),MID(MID(A1,FI ND(CHAR(32),A1)+1,99),MIN(SEARCH({0,1,2,3,4,5,6,7, 8,9},MID(A1,FIND(CHAR(32),A1)+1,99)&"0123456789")) ,99)))

If this post helps click Yes
---------------
Jacob Skaria

"nc" wrote:

I have an extract from a long list below. I would like to strip out the
second number from each text. i.e example from the first two rows are 1000
and 3104 respectively. Any help would be greatly appreciated.

010114 Long K 1000 Basic Pay
042140 Balard S 3104 FLS AD Hrs
040557 Sanjay Conroy A 3001 Fee
042316 Christie Eron C 1006 Basic Arrs
020674 De Thang C 1104 Basic-FLS
040203 De La Conjo MF 1000 Basic Pay
020025 Van Wanjo E 1010 Lon All'ce
040471 Van Kot FWM 1102 Basic-FLC
040471 Van Kot FWM 1112 L.A. - FLC

PS....

"JoeU2004" wrote:
If the first number is always the first 6 characters followed by
at least one character (e.g. space), and if the second number
is always 4 characters

More generally, the following UDF returns any second number, or the null
string if there is none.

Function extractNumber2(s As String) As String
Dim re As Object, nums As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
Set nums = re.Execute(s)
If nums.Count = 2 Then extractNumber2 = nums.Item(1)
End Function

If you are unfamiliar with VBA, the following works in Excel 2003, at least:

1. Click Tools Macro Security Medium.

2. Press alt+F11 to open a VB window.

3. In the VB window, click Insert Module. Copy-and-paste the above
function into the VB editor pane on the right.

4. In the Excel window, enter the following formula into any cell:

=--extractNumber2(A1)

That this returns a #VALUE error if there is no second number. You can
avoid the error using the following:

=if(extractNumber2(A1)="", "", --extractNumber2(A1))

Alternatively, omit "--" in the first formula if you want text instead of a
number.

FYI, "--" is simply double-negation. For example if X1 contains 5, =-X1
returns -5, and =--X1 returns 5. It is used here to convert a numeric
string into an actual number.

----- original message -----

"JoeU2004" wrote in message
...
"nc" wrote:
I have an extract from a long list below.

If the first number is always the first 6 characters followed by at least
one character (e.g. space), and if the second number is always 4
characters, then:

=--MID(A1, MIN(FIND({1,2,3,4,5,6,7,8,9,0}, A1&"1234567890", 8)), 4)

This returns a numeric result; format with a desired number format, if
necessary. Omit "--" if you want a text result; format with a desired
horizontal alignment, if necessary.

----- original message -----

"nc" wrote in message
...
I have an extract from a long list below. I would like to strip out the
second number from each text. i.e example from the first two rows are
1000
and 3104 respectively. Any help would be greatly appreciated.

010114 Long K 1000 Basic Pay
042140 Balard S 3104 FLS AD Hrs
040557 Sanjay Conroy A 3001 Fee
042316 Christie Eron C 1006 Basic Arrs
020674 De Thang C 1104 Basic-FLS
040203 De La Conjo MF 1000 Basic Pay
020025 Van Wanjo E 1010 Lon All'ce
040471 Van Kot FWM 1102 Basic-FLC
040471 Van Kot FWM 1112 L.A. - FLC

PS....

Arguably, a better design:

Function extractNumber(s As String, n As Integer)
Dim re, nums
extractNumber = ""
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
Set nums = re.Execute(s)
If nums.Count = n Then extractNumber = --nums.Item(n - 1)
End Function

I have generalized the function to return the n-th integer, if it exists.
Usage:

=extractNumber(A1,2)

returns the 2nd integer as a number (not text), or "" if none. If you
always want text, format the cell as Text.

Note that numbers of the form "-12.34" will be treated as two integers (12
and 34). Moreover, not that embedded numbers of the form "word123word" will
be treated as an integer (123). I chose to do that to make it easier to
understand the function, since it probably meets the OP's requirements. A
more general regular expression could be designed to recognize all numbers,
signed or not, with or without decimal fractions, excluding embedded
numbers.

----- original messages -----

"JoeU2004" wrote in message
...
PS....

"JoeU2004" wrote:
If the first number is always the first 6 characters followed by
at least one character (e.g. space), and if the second number
is always 4 characters

More generally, the following UDF returns any second number, or the null
string if there is none.

Function extractNumber2(s As String) As String
Dim re As Object, nums As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\d+"
Set nums = re.Execute(s)
If nums.Count = 2 Then extractNumber2 = nums.Item(1)
End Function

If you are unfamiliar with VBA, the following works in Excel 2003, at
least:

1. Click Tools Macro Security Medium.

2. Press alt+F11 to open a VB window.

3. In the VB window, click Insert Module. Copy-and-paste the above
function into the VB editor pane on the right.

4. In the Excel window, enter the following formula into any cell:

=--extractNumber2(A1)

That this returns a #VALUE error if there is no second number. You can
avoid the error using the following:

=if(extractNumber2(A1)="", "", --extractNumber2(A1))

Alternatively, omit "--" in the first formula if you want text instead of
a number.

FYI, "--" is simply double-negation. For example if X1 contains 5, =-X1
returns -5, and =--X1 returns 5. It is used here to convert a numeric
string into an actual number.

----- original message -----

"JoeU2004" wrote in message
...
"nc" wrote:
I have an extract from a long list below.

If the first number is always the first 6 characters followed by at least
one character (e.g. space), and if the second number is always 4
characters, then:

=--MID(A1, MIN(FIND({1,2,3,4,5,6,7,8,9,0}, A1&"1234567890", 8)), 4)

This returns a numeric result; format with a desired number format, if
necessary. Omit "--" if you want a text result; format with a desired
horizontal alignment, if necessary.

----- original message -----

"nc" wrote in message
...
I have an extract from a long list below. I would like to strip out the
second number from each text. i.e example from the first two rows are
1000
and 3104 respectively. Any help would be greatly appreciated.

010114 Long K 1000 Basic Pay
042140 Balard S 3104 FLS AD Hrs
040557 Sanjay Conroy A 3001 Fee
042316 Christie Eron C 1006 Basic Arrs
020674 De Thang C 1104 Basic-FLS
040203 De La Conjo MF 1000 Basic Pay
020025 Van Wanjo E 1010 Lon All'ce
040471 Van Kot FWM 1102 Basic-FLC
040471 Van Kot FWM 1112 L.A. - FLC