Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extract using MID function ?
I have cells with numbers of various character counts
seperated by an "x". eg. 2x1.5 48x3 I can find the "x" position with the MID function. Now how do I extract ALL numbers to the right of the "x" or ALL numbers to the left of the "x" ? Thank you, Emory |
#2
|
|||
|
|||
Everything before the x:
=--LEFT(A1,SEARCH("x",A1,1)-1) Everything after the x: =--MID(A1,SEARCH("x",A1,1)+1,255) =left() and =mid() both return strings. If you want to use those values as numbers, you can convert them by using -- in front of the expression. =search() and =find() are very similar functions. =Find() is case sensitive. =search() is not. Emory Richter wrote: I have cells with numbers of various character counts seperated by an "x". eg. 2x1.5 48x3 I can find the "x" position with the MID function. Now how do I extract ALL numbers to the right of the "x" or ALL numbers to the left of the "x" ? Thank you, Emory -- Dave Peterson |
#3
|
|||
|
|||
Numbers to the left of the"x":
=LEFT(A1,SEARCH("x",A1)-1)*1 Numbers to the right of the "x": =MID(A1,SEARCH("x",A1)+1,15)*1 I hope that helps. -- Regards, Ron |
#4
|
|||
|
|||
For RIGHT of the "X":
=RIGHT(A1,LEN(A1)-SEARCH("X",A1)) For LEFT of the "X": =LEFT(A1,SEARCH("x",A1)-1) And if you want *real* numbers: =--RIGHT(A1,LEN(A1)-SEARCH("X",A1)) =--LEFT(A1,SEARCH("x",A1)-1) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Emory Richter" wrote in message om... I have cells with numbers of various character counts seperated by an "x". eg. 2x1.5 48x3 I can find the "x" position with the MID function. Now how do I extract ALL numbers to the right of the "x" or ALL numbers to the left of the "x" ? Thank you, Emory |
#5
|
|||
|
|||
Dave
Thanks for the methods. One thing, In SEARCH("x",A1,1) I understand that the "x" is the thing_to_search and A1 is the source. What is the third item, the 1? In article , ec35720 @netscapeXSPAM.com says... Everything before the x: =--LEFT(A1,SEARCH("x",A1,1)-1) Everything after the x: =--MID(A1,SEARCH("x",A1,1)+1,255) =left() and =mid() both return strings. If you want to use those values as numbers, you can convert them by using -- in front of the expression. =search() and =find() are very similar functions. =Find() is case sensitive. =search() is not. Emory Richter wrote: I have cells with numbers of various character counts seperated by an "x". eg. 2x1.5 48x3 I can find the "x" position with the MID function. Now how do I extract ALL numbers to the right of the "x" or ALL numbers to the left of the "x" ? Thank you, Emory |
#6
|
|||
|
|||
Ron
Thanks for the methods. One thing, at the end of the function you seem to multiply by 1 [ *1 ]. What is the purpose? Emory In article , says... Numbers to the left of the"x": =LEFT(A1,SEARCH("x",A1)-1)*1 Numbers to the right of the "x": =MID(A1,SEARCH("x",A1)+1,15)*1 I hope that helps. |
#7
|
|||
|
|||
|
#8
|
|||
|
|||
The functions we're using return text strings. To make Excel convert the text
to numbers, we need to use an arithmetic operator. You'll see, at various times, we multiply the result by 1 OR we'll preceed the result with -- (two minus signs which offset each other). Also, we only do that if the expected result will be: Numeric text: to return a number or Boolean (true/false): to convert TRUE to 1, FALSE to 0 (zero) Otherwise, an error is returned. (There is no such thing as negative "Tree") Does that help? -- Regards, Ron |
#9
|
|||
|
|||
|
#10
|
|||
|
|||
It's the starting position for the search. The default is 1, so I didn't have
to use it. But if I wanted to ignore the first 2 characters, I could: =SEARCH("x",A1,3) so if A1 contained: xxasdfxqwer that formula would return: 7 Excel's help is a nice spot to look for these kinds of questions <bg. Emory Richter wrote: Dave Thanks for the methods. One thing, In SEARCH("x",A1,1) I understand that the "x" is the thing_to_search and A1 is the source. What is the third item, the 1? In article , ec35720 @netscapeXSPAM.com says... Everything before the x: =--LEFT(A1,SEARCH("x",A1,1)-1) Everything after the x: =--MID(A1,SEARCH("x",A1,1)+1,255) =left() and =mid() both return strings. If you want to use those values as numbers, you can convert them by using -- in front of the expression. =search() and =find() are very similar functions. =Find() is case sensitive. =search() is not. Emory Richter wrote: I have cells with numbers of various character counts seperated by an "x". eg. 2x1.5 48x3 I can find the "x" position with the MID function. Now how do I extract ALL numbers to the right of the "x" or ALL numbers to the left of the "x" ? Thank you, Emory -- Dave Peterson |
#11
|
|||
|
|||
In article , ec35720
@netscapeXSPAM.com says... t's the starting position for the search. The default is 1, so I didn't have to use it. But if I wanted to ignore the first 2 characters, I could: =SEARCH("x",A1,3) so if A1 contained: xxasdfxqwer that formula would return: 7 Excel's help is a nice spot to look for these kinds of questions <bg. Dave thanks for taking the time to write out the explaination. I am so used to looking in my old book I never thought of Excel Help. But the info is there. Emory |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Function required to extract cell info from an unopened file. | Excel Worksheet Functions |