Position in a string
 

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.

Position in a string
 

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

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

Position in a string
 

Can I use in-built excel functions?

Position in a string
 

Can I use excel formulae?

Position in a string
 

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

wrote in message
ups.com...
Can I use excel formulae?

Position in a string
 

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(IN DIRECT("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