Formula amendment to not remove digits

[Guest]

I have a list in Col I that shows data in the the following way:

AR All Risks AR
AV Agricultural Vehicle AG
BD Breakdown

Each cell has: an initial code, 5 spaces, the name, then a random number of
spaces and finally (in some cases), another code which may or may not match
the first code.

I need to break down the information from col I into 2 sections. In col J I
would like to show the first code (the 2 digit code) and in col K, I'd like
to show the name but not any trailing information.

I've achieved this by using the following formulas:

In Col J:
=IF(I8="","",(LEFT(I8,2)))
This will result in a value of 'AR, AV, BD' - as per the example above.

In Col K:
=MID(TRIM(LEFT(I8,LEN(I8)-2)),4,999)
This will result in a value of 'All Risks, Agricultural Vehicle'

However, where a second code isn't present a Col I cell (as per 'Breakdown'
above), the col K formula still removes the last two digits, therefore
displaying 'Breakdo'.

The problem is that there's no consistency - some have codes, some don't and
those that do, don't necessarily match the first code. Is there a way
though, of getting the result I need by adding something else to the Col K
formula?

 

[Guest]

Look at Data=>Text to Columns to separate.

 

[Ron Rosenfeld]

I have a list in Col I that shows data in the the following way:

AR All Risks AR
AV Agricultural Vehicle AG
BD Breakdown

Each cell has: an initial code, 5 spaces, the name, then a random number of
spaces and finally (in some cases), another code which may or may not match
the first code.

I need to break down the information from col I into 2 sections. In col J I
would like to show the first code (the 2 digit code) and in col K, I'd like
to show the name but not any trailing information.

I've achieved this by using the following formulas:

In Col J:
=IF(I8="","",(LEFT(I8,2)))
This will result in a value of 'AR, AV, BD' - as per the example above.

In Col K:
=MID(TRIM(LEFT(I8,LEN(I8)-2)),4,999)
This will result in a value of 'All Risks, Agricultural Vehicle'

However, where a second code isn't present a Col I cell (as per 'Breakdown'
above), the col K formula still removes the last two digits, therefore
displaying 'Breakdo'.

The problem is that there's no consistency - some have codes, some don't and
those that do, don't necessarily match the first code. Is there a way
though, of getting the result I need by adding something else to the Col K
formula?

Here is a method using a UDF.

<alt-F11> opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

From the top menu of the VB Editor, Tools/References and select "Microsoft
VBScript Regular Expressions 5.5"

To use the formula with your data in A1, you can use the following:

Initial Code: =regex($A1,1)
Name: =regex($A1,2)
Final Code: =regex($A1,3)

A valid entry starts with two capital letters followed by at least one <space>.
The Final code is optional.

The format is set in .Pattern below

=============================================
Option Explicit
Function regex(str As String, Optional br As Long = 1) As String
'Requires Reference set to Microsoft VBScript Regular Expressions 5.5
Dim re As RegExp
Dim mc As MatchCollection
Dim m As Match
Set re = New RegExp
With re
.MultiLine = True
.Global = True
.Pattern = "([A-Z]{2})(.*?)([A-Z]{2})?$"
.IgnoreCase = False
End With
If re.Test(str) = True Then
Set mc = re.Execute(str)
regex = mc(0).SubMatches(br - 1)
End If
End Function

--ron

 

[Guest]

Good thinking. I didn't realise that function would remove leading/trailing
spaces.

Thanks for the help!