| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting numbers from a cell with carriage returns
I'm looking for the formula that would extract the numbers in a cell like below in to three seperate cells: This is the contents of one cell: _______ | 3.3 | | 9.5 | | 12.5 | ----- I know "=LEFT(A1,3)" will give me 3.3 and "=RIGHT(A1,4)" will give me 12.5 but....what if 3.3 was 10.3? My formula would only give me 0.3. And... I need to extract 9.5 somehow (which could be -X.X or XX.X) Is there a way to count text strings before and after carriage returns??? spaces might work in this case, too... -- kaytoo ------------------------------------------------------------------------ kaytoo's Profile: http://www.excelforum.com/member.php...o&userid=34057 View this thread: http://www.excelforum.com/showthread...hreadid=538210 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting numbers from a cell with carriage returns
Insert a number of empty columns to the right, select the column, do
datatext to columnsselect delimited, click next, select other and click in the other box, hold down alt key while typing 010 on the numpad, release the alt key, click finish. Then copy the newly extracted numbers and do editpaste special and select transpose to paste them into one column -- Regards, Peo Sjoblom http://nwexcelsolutions.com "kaytoo" wrote in message ... I'm looking for the formula that would extract the numbers in a cell like below in to three seperate cells: This is the contents of one cell: _______ | 3.3 | | 9.5 | | 12.5 | ----- I know "=LEFT(A1,3)" will give me 3.3 and "=RIGHT(A1,4)" will give me 12.5 but....what if 3.3 was 10.3? My formula would only give me 0.3. And... I need to extract 9.5 somehow (which could be -X.X or XX.X) Is there a way to count text strings before and after carriage returns??? spaces might work in this case, too... -- kaytoo ------------------------------------------------------------------------ kaytoo's Profile: http://www.excelforum.com/member.php...o&userid=34057 View this thread: http://www.excelforum.com/showthread...hreadid=538210 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting numbers from a cell with carriage returns
That is helpful, but i will be doing this for many, many cells... I found this formula to get me the first number: =LEFT(A1,FIND(".",A1)+1) I'm working on a =MID formula for the middle that counts from the period after the carriage return... I found you can do this: =MID(C43,(FIND(" ",C43)+1),3) I just need to figgure out how to make the "3" a Find formula that finds the "." after the carriage return -- kaytoo ------------------------------------------------------------------------ kaytoo's Profile: http://www.excelforum.com/member.php...o&userid=34057 View this thread: http://www.excelforum.com/showthread...hreadid=538210 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting numbers from a cell with carriage returns
That's the whole point, you can do a whole column at once
Peo "kaytoo" wrote in message ... That is helpful, but i will be doing this for many, many cells... I found this formula to get me the first number: =LEFT(A1,FIND(".",A1)+1) I'm working on a =MID formula for the middle that counts from the period after the carriage return... I found you can do this: =MID(C43,(FIND(" ",C43)+1),3) I just need to figgure out how to make the "3" a Find formula that finds the "." after the carriage return -- kaytoo ------------------------------------------------------------------------ kaytoo's Profile: http://www.excelforum.com/member.php...o&userid=34057 View this thread: http://www.excelforum.com/showthread...hreadid=538210 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extracting numbers from a cell with carriage returns
That is the result I want.. there isnt a formula for that? I want to avoid doing that process everytime I download a worksheet. -- kaytoo ------------------------------------------------------------------------ kaytoo's Profile: http://www.excelforum.com/member.php...o&userid=34057 View this thread: http://www.excelforum.com/showthread...hreadid=538210 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| How to put hard returns in a cell without going to the next cell | Excel Discussion (Misc queries) | |||
| Using Jet to read excel file returns blank for last cell - sometim | Excel Discussion (Misc queries) | |||
| Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
| GET.CELL | Excel Worksheet Functions | |||
Hybrid Mode
