Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text function
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text function
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text function
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text function
"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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text function
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text function
=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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text function
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a conditional suffix in text function format syntax=text(value,format_text) | Excel Worksheet Functions | |||
Can Text Function change output text color? | Excel Discussion (Misc queries) | |||
Advanced text function (combining text) | Excel Worksheet Functions | |||
Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) |