Hello all, I've ran into trouble in terms of coding for my excel spreadsheet.
D2: 259 1/2IN 40D ISOPRENE
D3: 15/16 IR 40D (176 3/4)
D4: 153 3/4IN 40D NEOPRENE
If I want to search for the inch in each text and only return the size(i.e 259 1/2 for D2, 176 3/4 for D3. 153 3/4, for D4) respectively, how would I go about doing this.
I'd really appreciate all your help and hope you are having a wonderful day.
keyword matching in a cell(compliated)
Hi, thank god for all you intellectuals to possibly help me!
Book1.xlsx
As you can see from the attached, I have a list of products in Column A and in Column B
I just want to size to be returned(i.e 252 1/2 for B2, 176 3/4 for B3, 192 1/2 for B4, 153 3/4 for B6)
Notice that the digits of the size can be different(i.e A24 only has 60 3/4). The point is, size is not strictly restricted to 2 or 3 digits.
For me this looks complicated to put all into one function so I thought there could be a way to use IF&OR to possibly tackle this problem.
Any help would be greatly appreciated.
Thank you and have a great day!
For all of the entries, is the first letter "I"?
Please click the * icon below if I have helped.
hk4kim,
Welcome to the forum!
Using your provided example file, use this formula in cell B2 and copy down:
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Yes it is.
This should do it:
Formula:
Please Login or Register to view this content.
Do you mind explainig what this does and what -1 is?
The LEFT function has two portions:
1 - The cell you are looking in
2 - The # of characters you want to have returned.
The FIND function returns the location of a character in a cell. It has three portions:
1 - The Character you want to find (in this case "I")
2 - The cell you are looking in
3 - Where to start looking (by putting "1" you look at the very start of the cell).
The TRIM removes any extra spaces (if they exist).
So for you example:
The find function, for D21, will return 8, because the "I" is the 8th character in cell D21. we then add a "-1" because you want the first 7 characters to be returned. (if the I was the 9th character, you would want the first 8 characters returned.
The left function looks at D21 and returns the first X characters of the cell, dependant on what the find function returns. Does that make sense?
Thank you for your help!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
D2
15/16 IR
E2
=ISNUMBER(TRIM(LEFT(D2,FIND("I",D2,1)-1)))
Hi all, when I perform this function it returns false, how can i make so that the returned value from this formula 15/16 will return as number in this case TRUE?
Last edited by hk4kim; 05-07-2013 at 09:06 AM.
=isnumber(value(trim(left(d2,find("i",d2,1)-1))))
Elegant Simplicity............. Not Always
still get false...
=isnumber(value(trim(left(d2,find("i",d2,1)-2))))
It's because Excel is seeing it as a date but you can't have the 15th of the 16th you'll need to break the numbers down by pairs.
Last edited by AndyLitch; 05-07-2013 at 09:26 AM.
i mean how does 15/1 count as a number while 15/16 does not?
Because Excel Sees 15/1 as a Date (January 15th)Originally Posted by hk4kim
And dates are really just serial numbers incrimenting by 1 since january 1 1900.
But 15/16 does not appear to be a date, so it's just a Text String - Not a number.
if i do =isnumber(15/16), it returns true
Because that is being read as 15 devided by 16.
And it is not encosed in Quotes, so Excel doesn't intially see it as a text string.
The LEFT function returns a TEXT string.
Try
=ISNUMBER("15/16")
When excel encounters a Text string that looks like it might be a date, then it converts it to a date.
Last edited by Jonmo1; 05-07-2013 at 09:36 AM.
because it sees the 1 as January
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks