Excel extraction help

[map6122]

I have a lot of data and I want to write a formula.
I have four companies in my data XYZ, XY, AB and CD. as you can see in below data. Original Data sometime contains company name sometimes there is no company name in front of original data. I want to keep same original data in desired output column if there is no company name. If there is a company name in Original data than I would like to remove or throw that company name in different column.
Thanks,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Original Data[/TD]
[TD]Desired Output[/TD]
[TD]Comments to understand more in details.[/TD]
[/TR]
[TR]
[TD]IDS[/TD]
[TD]IDS[/TD]
[TD]No company in front of original data so output is same.[/TD]
[/TR]
[TR]
[TD]ACK[/TD]
[TD]ACK[/TD]
[TD]No company in front of original data so output is same.[/TD]
[/TR]
[TR]
[TD]XYZIDS[/TD]
[TD]IDS[/TD]
[TD]Original data XYZ is a company name[/TD]
[/TR]
[TR]
[TD]XYIDS[/TD]
[TD]IDS[/TD]
[TD]Original data XY is a company name[/TD]
[/TR]
[TR]
[TD]XYZking[/TD]
[TD]King[/TD]
[TD]Original data XYZ is a company name[/TD]
[/TR]
[TR]
[TD]ABIDS[/TD]
[TD]IDS[/TD]
[TD]AB is a company name[/TD]
[/TR]
[TR]
[TD]CDACK[/TD]
[TD]ACK[/TD]
[TD]CD is a company name[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

How would we know where the company name ends and the data to ignore starts? You have used dummy names, so it is hard to tell. Can you give more realist names?

 

[Carpaccio]

Hi mpa6122
It looks like the Co name is in column 1 and you didn't say how many rows, so I've used 100.
I've just done Company CD, but you can repeat it fr your other companies, as there is only four it shouldn't be a big task.
As a start, you can give this a try:

Sub RemoveCoName()
Dim RemoveCoName As String
For i = 1 To 100

RemoveCoName = Cells(i, 1)
If Left(RemoveCoName, 2) = "CD" Then
If Len(RemoveCoName) > 2 Then
Cells(i, 1) = Right(RemoveCoName, Len(RemoveCoName) - 2)
End If
End If
Next i
End Sub

Hope that helps
Regards
Alan

 

[map6122]

How would we know where the company name ends and the data to ignore starts? You have used dummy names, so it is hard to tell. Can you give more realist names?

Ford
I do not want data to be ignored
I want companies to be ignored. COmpany name is 2 or 3 word max no numbers.

Company generic names.

XYZ
XY
ABC
AB

 

[map6122]

Alan sorry i'm very new to excel. is it a Macro code? also will it keep rest of the data after company name? Because that's what really I'm after.

 

[FDibbins]

Ford
I do not want data to be ignored
I want companies to be ignored. COmpany name is 2 or 3 word max no numbers.

Company generic names.

XYZ
XY
ABC
AB

What I meant was, you have a company name, and then some other text - how would we know where the name ends and the text starts?

Generic letters like that don't help much...are there spaces/is there a list of names/what rules would be used so split the data?
Some look to have 2 letters, some 3 (are there 4 or 5?) - how are we to know which is which?

 

[Carpaccio]

Hi map6122
Yes, it's a macro and it will just remove the matching characters from the start of the cell.

 

[Carpaccio]

perhaps an alternative is to use Find/Replace - you can replace the Company name with nothing, the limitation here is that it won't just look at the start of the company name, so it will replace any matches, so is replacing AB, NAB would become just N.