Position in a string

[[email protected]]

Here's a column from an excel I'm working on

column A
row1: sdfsdk;;;;;sdkfjsdfk
row2: fklasdfja;;;;;asdfsdafsd;;;;;
row3: sdf;sdfas;;sdfsf;;;;;
row4: sfkd;sdfsdaf;sfdsfds;;

I want to write a formula that will
1) remove last five characters only if all of them are colons, that is,
;;;;;
2) remove all the colons at the end, no matter how many.

For both the formulas, none of the colons in the middle should not be
affected. The length of each row is uncertain.

[Niek Otten]

1:

=IF(RIGHT(A1,5)=";;;;;",LEFT(A1,LEN(A1)-5),A1)

2: I think this requires a UDF.

Here is one, if you're new to VBA, look here first:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Function RemoveTrailingChars(a As String, b As String)
Dim i As Long
For i = Len(a) To 1 Step -1
If Mid$(a, i, 1) <> b Then Exit For
Next i
RemoveTrailingChars = Left(a, i)
End Function


To be called with
=removetrailingchars(A1,";")

--
Kind regards,

Niek Otten

<[email protected]> wrote in message
news:[email protected]...
> Here's a column from an excel I'm working on
>
> column A
> row1: sdfsdk;;;;;sdkfjsdfk
> row2: fklasdfja;;;;;asdfsdafsd;;;;;
> row3: sdf;sdfas;;sdfsf;;;;;
> row4: sfkd;sdfsdaf;sfdsfds;;
>
> I want to write a formula that will
> 1) remove last five characters only if all of them are colons, that is,
> ;;;;;
> 2) remove all the colons at the end, no matter how many.
>
> For both the formulas, none of the colons in the middle should not be
> affected. The length of each row is uncertain.
>

[[email protected]]

Can I use in-built excel functions?

[[email protected]]

Can I use excel formulae?

[Niek Otten]

For the first one, yes (I gave you one)

For the second one: possibly, but I can't think of it yet.

But if you never have more than 6 trailing semicolons, it could be done like
this:

=IF(RIGHT(A1,6)=";;;;;;",LEFT(A1,LEN(A1)-6),IF(RIGHT(A1,5)=";;;;;",LEFT(A1,LEN(A1)-5),IF(RIGHT(A1,4)=";;;;",LEFT(A1,LEN(A1)-4),IF(RIGHT(A1,3)=";;;",LEFT(A1,LEN(A1)-3),IF(RIGHT(A1,2)=";;",LEFT(A1,LEN(A1)-2),IF(RIGHT(A1,1)=";",LEFT(A1,LEN(A1)-1),A1))))))


--
Kind regards,

Niek Otten

<[email protected]> wrote in message
news:[email protected]...
> Can I use excel formulae?
>

[vezerid]

First of all, your first condition seems a subcondition of the second,
because you want the last set of ;;; to be removed, and this includes
the number 5. Give this, the following *array* formula will remove the
last set of ;;; regardless of length:

=LEFT(A13,LEN(A13)-MAX(ROW(INDIRECT("1:"&LEN(A13)))*(RIGHT(A13,ROW(INDIRECT("1:"&LEN(A13))))=REPT(";",ROW(INDIRECT("1:"&LEN(A13)))))))

Since this is an array formula, it must be committed with
Shift+Ctrl+Enter.

HTH
Kostis Vezerides