Please help on an Excel spreadsheet for work

[Hayden Fox]

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.

[Biff]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Elkar]

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
>
>

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>

[Kevin Vaughn]

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
>
>