Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help on an Excel spreadsheet for work
Sorry to have posted this in 2 places but after my first post I realized that this board is the better one to post on as it states on the main page. Here is my problem. In COLUMN A, there is a list of cells containing numeric & text information. For example: Column A/Row 1 = 123456 Keyboards, Black Column A/Row 2 = 1234 Mice, Black Column A/Row 3 = 12345 Monitors, Gray In COLUMN B, there is a repeated list of text all the way down the column. For example: Column B/Row 1 = Text""Text Column B/Row 2 = Text""Text Column B/Row 3 = Text""Text My goal here is to take the numeric portion from each cell in Column A and place that numeric portion in between the Quotation Marks ("") in the corresponding cell in Column B. I tried to use the Help feature but after one hour of trying that still have no luck. Please help me if you can. I will pay you back someday somehow if you do. -- Hayden Fox ------------------------------------------------------------------------ Hayden Fox's Profile: http://www.excelforum.com/member.php...o&userid=30807 View this thread: http://www.excelforum.com/showthread...hreadid=504680 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help on an Excel spreadsheet for work
Hi!
Here's a formula method that requires an additional column. In C1 enter this formula: =LEFT(B1,FIND("""",B1)-1)&""""&LEFT(A1,FIND(" ",A1)-1)&""""&MID(B1,FIND("""""",B1)+2,255) Copy down as needed. Biff "Hayden Fox" wrote in message ... Sorry to have posted this in 2 places but after my first post I realized that this board is the better one to post on as it states on the main page. Here is my problem. In COLUMN A, there is a list of cells containing numeric & text information. For example: Column A/Row 1 = 123456 Keyboards, Black Column A/Row 2 = 1234 Mice, Black Column A/Row 3 = 12345 Monitors, Gray In COLUMN B, there is a repeated list of text all the way down the column. For example: Column B/Row 1 = Text""Text Column B/Row 2 = Text""Text Column B/Row 3 = Text""Text My goal here is to take the numeric portion from each cell in Column A and place that numeric portion in between the Quotation Marks ("") in the corresponding cell in Column B. I tried to use the Help feature but after one hour of trying that still have no luck. Please help me if you can. I will pay you back someday somehow if you do. -- Hayden Fox ------------------------------------------------------------------------ Hayden Fox's Profile: http://www.excelforum.com/member.php...o&userid=30807 View this thread: http://www.excelforum.com/showthread...hreadid=504680 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help on an Excel spreadsheet for work
Try this formula in Column C. Assuming all of your data follows the formats
you provided, this should work: =MID(B1,1,FIND("""",B1,1))&MID(A1,1,FIND(" ",A1,1)-1)&MID(B1,FIND("""",B1,1)+1,1024) HTH, Elkar "Hayden Fox" wrote: Sorry to have posted this in 2 places but after my first post I realized that this board is the better one to post on as it states on the main page. Here is my problem. In COLUMN A, there is a list of cells containing numeric & text information. For example: Column A/Row 1 = 123456 Keyboards, Black Column A/Row 2 = 1234 Mice, Black Column A/Row 3 = 12345 Monitors, Gray In COLUMN B, there is a repeated list of text all the way down the column. For example: Column B/Row 1 = Text""Text Column B/Row 2 = Text""Text Column B/Row 3 = Text""Text My goal here is to take the numeric portion from each cell in Column A and place that numeric portion in between the Quotation Marks ("") in the corresponding cell in Column B. I tried to use the Help feature but after one hour of trying that still have no luck. Please help me if you can. I will pay you back someday somehow if you do. -- Hayden Fox ------------------------------------------------------------------------ Hayden Fox's Profile: http://www.excelforum.com/member.php...o&userid=30807 View this thread: http://www.excelforum.com/showthread...hreadid=504680 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help on an Excel spreadsheet for work
Depending on how you view these groups, the formula may be affected by line
wrap. I use OE and this is the case. In the 2nd FIND function there is a space between the quotes: .......LEFT(A1,FIND(" ",A1)-1)....... Biff "Biff" wrote in message ... Hi! Here's a formula method that requires an additional column. In C1 enter this formula: =LEFT(B1,FIND("""",B1)-1)&""""&LEFT(A1,FIND(" ",A1)-1)&""""&MID(B1,FIND("""""",B1)+2,255) Copy down as needed. Biff "Hayden Fox" wrote in message ... Sorry to have posted this in 2 places but after my first post I realized that this board is the better one to post on as it states on the main page. Here is my problem. In COLUMN A, there is a list of cells containing numeric & text information. For example: Column A/Row 1 = 123456 Keyboards, Black Column A/Row 2 = 1234 Mice, Black Column A/Row 3 = 12345 Monitors, Gray In COLUMN B, there is a repeated list of text all the way down the column. For example: Column B/Row 1 = Text""Text Column B/Row 2 = Text""Text Column B/Row 3 = Text""Text My goal here is to take the numeric portion from each cell in Column A and place that numeric portion in between the Quotation Marks ("") in the corresponding cell in Column B. I tried to use the Help feature but after one hour of trying that still have no luck. Please help me if you can. I will pay you back someday somehow if you do. -- Hayden Fox ------------------------------------------------------------------------ Hayden Fox's Profile: http://www.excelforum.com/member.php...o&userid=30807 View this thread: http://www.excelforum.com/showthread...hreadid=504680 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help on an Excel spreadsheet for work
This formula would go in column C (and if you wanted, you could copy
pastespecial values to column B.) This assumes that the cells in A always start with the number and a space: =LEFT(B1,FIND("""",B1)-1)& """" & LEFT(A1, FIND(" ",A1)-1) & """" & RIGHT(B1,LEN(B1)-(FIND("""",B1)+1)) If the text in column B was as constant as your example implies, you could enter the following formula in Column B and copy down: ="Text"& """" & LEFT(A1, FIND(" ",A1)-1) & """" & "Text" -- Kevin Vaughn "Hayden Fox" wrote: Sorry to have posted this in 2 places but after my first post I realized that this board is the better one to post on as it states on the main page. Here is my problem. In COLUMN A, there is a list of cells containing numeric & text information. For example: Column A/Row 1 = 123456 Keyboards, Black Column A/Row 2 = 1234 Mice, Black Column A/Row 3 = 12345 Monitors, Gray In COLUMN B, there is a repeated list of text all the way down the column. For example: Column B/Row 1 = Text""Text Column B/Row 2 = Text""Text Column B/Row 3 = Text""Text My goal here is to take the numeric portion from each cell in Column A and place that numeric portion in between the Quotation Marks ("") in the corresponding cell in Column B. I tried to use the Help feature but after one hour of trying that still have no luck. Please help me if you can. I will pay you back someday somehow if you do. -- Hayden Fox ------------------------------------------------------------------------ Hayden Fox's Profile: http://www.excelforum.com/member.php...o&userid=30807 View this thread: http://www.excelforum.com/showthread...hreadid=504680 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Importing an Excel spreadsheet from the web. | Excel Discussion (Misc queries) | |||
Delay to startup excel spreadsheet | Excel Worksheet Functions | |||
copy and paste excel spreadsheet into autocad | Excel Discussion (Misc queries) | |||
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? | Excel Discussion (Misc queries) |