Duplicate Text within single cell. How to identify & split

[TeRex82]

Greetings,

I have searched hi & lo for any help on this, but drawn a blank so far.

I have data which I have combined from 2 cells to 1 cell. {see Example1}

{Example1}

ColA: ColB:
Hardware Vendor

Cisco Cisco 1700

I have used the text formula =A2&" "&B2, so come up with...

"Cisco Cisco 1700" in ColB

The catch is, sometime the Hardware in Col A is formatted correctly, so the
text string works correctly. see {Example2}

Example2

ColA: ColB:
HP\Compaq DL 585 G1

this returns

"HP\Compaq DL 585 G1", which is perfect.

I have near on 400 cells, 50% of which are in the incorrect format..so the
Vendor name is appearing twice.

Need to identify there duplications and split them out.

Any ideas would be much appreciated.

To change the way I receive the original data, would be an insurmountable
challenge.

This is how I receive the data.

Source:
Several Thousand Records on a sheet, many, many duplications.
Pivot Table created to reduce duplications to single instances

One I receive Data:
Formula used to extract from Pivot Table is simple, =A, =B
Then =A2&" "&B2 used to get the list with lots of duplications


Thanks for taking the time to read this,

Regards

TerryH

[Mallycat]

you've posted everything other than a sample of what the incorrect data looks like. I will assume "HP\Compaq DL 585 G1", looks like "
HP HP\Compaq DL 585 G1", ie with a space.

This forumula searches to see if there is a space. If there is, it takes the first half of the cell and uses that

=IF(ISERR(SEARCH(" ",A2)),A2,LEFT(A2,SEARCH(" ",A2)-1)&" "&B2)

[Bearacade]

I am not sure if I am understanding you correct, but here is a stab in the dark.

I am assuming the incorrect version has the Vendor name filled, and the vendor name along with the hardward name in the Hardware field.

Maybe this will help, it looks to see if the Vendor name reoccurs in the beginning of the hardware field:

=IF(LEFT(B1,LEN(A1))=A1,B1,A1&" "&B1)

So if you apply it to the Cisco example, It will not just show Cisco 1700

[CLR]

These things are tricky, and nothing will solve all the probs, but this
should help. Put this in a helper column, copied down...........

=IF(A1=(LEFT(B1,FIND(" ",B1,1)-1)),B1,A1&" "&B1)

Vaya con Dios,
Chuck, CABGx3


"TeRex82" <[email protected]> wrote in message
news:[email protected]...
> Greetings,
>
> I have searched hi & lo for any help on this, but drawn a blank so far.
>
> I have data which I have combined from 2 cells to 1 cell. {see Example1}
>
> {Example1}
>
> ColA: ColB:
> Hardware Vendor
>
> Cisco Cisco 1700
>
> I have used the text formula =A2&" "&B2, so come up with...
>
> "Cisco Cisco 1700" in ColB
>
> The catch is, sometime the Hardware in Col A is formatted correctly, so
the
> text string works correctly. see {Example2}
>
> Example2
>
> ColA: ColB:
> HP\Compaq DL 585 G1
>
> this returns
>
> "HP\Compaq DL 585 G1", which is perfect.
>
> I have near on 400 cells, 50% of which are in the incorrect format..so the
> Vendor name is appearing twice.
>
> Need to identify there duplications and split them out.
>
> Any ideas would be much appreciated.
>
> To change the way I receive the original data, would be an insurmountable
> challenge.
>
> This is how I receive the data.
>
> Source:
> Several Thousand Records on a sheet, many, many duplications.
> Pivot Table created to reduce duplications to single instances
>
> One I receive Data:
> Formula used to extract from Pivot Table is simple, =A, =B
> Then =A2&" "&B2 used to get the list with lots of duplications
>
>
> Thanks for taking the time to read this,
>
> Regards
>
> TerryH
>

[TeRex82]

All suggestions work fine, I have gone with ...

=IF(LEFT(B1,LEN(A1))=A1,B1,A1&" "&B1)

Many thanks for your assistance..with limited info supplied

Will give more details next time I post,


"Bearacade" wrote:

>
> I am not sure if I am understanding you correct, but here is a stab in
> the dark.
>
> I am assuming the incorrect version has the Vendor name filled, and the
> vendor name along with the hardward name in the Hardware field.
>
> Maybe this will help, it looks to see if the Vendor name reoccurs in
> the beginning of the hardware field:
>
> =IF(LEFT(B1,LEN(A1))=A1,B1,A1&" "&B1)
>
> So if you apply it to the Cisco example, It will not just show Cisco
> 1700
>
>
> --
> Bearacade
>
>
> ------------------------------------------------------------------------
> Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
> View this thread: http://www.excelforum.com/showthread...hreadid=553833
>
>