S
Sarah (OGI)
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?
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?