Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TeRex82
 
Posts: n/a
Default Duplicate Text within single cell. How to identify & split

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mallycat
 
Posts: n/a
Default Duplicate Text within single cell. How to identify & split


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)


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=553833

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bearacade
 
Posts: n/a
Default Duplicate Text within single cell. How to identify & split


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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Duplicate Text within single cell. How to identify & split

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" wrote in message
...
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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TeRex82
 
Posts: n/a
Default Duplicate Text within single cell. How to identify & split

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to I copy text from a range of cells to another single cell? WRT Excel Discussion (Misc queries) 2 December 18th 05 07:17 AM
Formula to transfer text from merged cells to single cell? rosey1 Excel Discussion (Misc queries) 2 October 11th 05 03:10 PM
Can I create an IF formula for single cell w/ dif. text in Excel? bennymichael Excel Discussion (Misc queries) 1 July 21st 05 08:59 PM
Split Long Text Cell into Two Shorter Cells Without Splitting Word Naomi T Excel Discussion (Misc queries) 1 July 7th 05 06:49 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 03:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"