Software

Ask something
Question Locked

excel searching thru a row or column

0 helpful
2 replies

by crojo1

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

im stuck! Please help, i need to search inside a given column or row, a column that has numerical and text values and they can change thru time.

I am a doctor, so i keep a record of my everyday work, with a spreadsheet that has the age, gender, so forth values.

One value which is the diagnosis and it can have numbers and mostly letters, every patient has a diagnosis, so what i want to search is inside that diagnosis column and if it finds a number, asign it to a category. See pregnancy is divided into three trimesters, so i want to search within the diagnosis if the trimester is a first, second or third trimester. Example: "Pregnancy. 25 WG" another value could be "Otitis" yada yada yada.

So i want to have the spreadsheet to return an "x" to a given cell if it finds the value within the diagnosis values, such as "Pregnancy 25 WG" belongs to a Second Trimester Pregnancy, and it would give the "Second Trimester" an "x" value to that specific Patient.

Any help is greatly apreciated, btw ive read the help topics and examples but they are not really explaining how it works (inside Office Help) Functions already tried FIND,FINDH,FINDV, COUNT...(but dont wanna countem but to search thru) thanks again.

2 replies

by locolobo

It looks to me like you want to extract the number from your value. Here is a website to use to try it.

http://office.microsoft.com/en-us/excel/HA011549011033.aspx?pid=CL100570551033

You MUST use CTL-Shift-Enter when entering this formula. The final formula given is

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

When you enter it using CTL-Shift-Enter you should see brackets {} around your formula.

For your example you will need to change the A1 to point to where your data is. Change the ROW($1:$9) to something larger like ROW($1:$50). Any changes to your formula don't forget to use CTL-Shift-Enter. I did finally get this to work, but I don't think I fully understand it yet.

Hope this helps.

This discussion is closed to new replies.