We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Stripping a number at the end of a text [closed]

asked 2014-03-20 19:19:39 +0200

Hermes14 gravatar image

updated 2020-08-16 12:46:37 +0200

Alex Kemp gravatar image

In column G I have a list of names. Some of the names have a number at the end & some don't. To remove the number at the end of the name I use:

=REPLACE(A2,SEARCH("[0-9]",A2,2),LEN(A2)-SEARCH("[0-9]",A2,2)," ")

This works fine with the names with numbers at the end but as soon as I get a name without a number I get an error

Libre office version 4.1.5.3 Calc

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 08:30:59.082572

3 Answers

Sort by » oldest newest most voted
1

answered 2014-03-20 22:37:38 +0200

m.a.riosv gravatar image

updated 2014-03-20 22:38:14 +0200

The problem is SEARCH() returns an error if find nothing.
Capturing the error like in next formula must solve the issue:
=LEFT(A2,IFERROR(SEARCH("[0-9]",A2,2)-1,LEN(A2)))

edit flag offensive delete link more

Comments

Alternate regex (no need for offset correction): =LEFT(A1;SEARCH("[A-Z][0-9]*$";A1;1))

oweng gravatar imageoweng ( 2014-03-21 06:18:34 +0200 )edit
0

answered 2014-03-21 10:26:33 +0200

Hermes14 gravatar image

Thanks mariosv

The formula you have given me worked perfectly this morning but when I tried to open my calc doc just now it is hanging. I should have said my os is opensuse 13.1

edit flag offensive delete link more

Comments

The problem seems to be opensuse. I see other people have the same problem as soon as they try to do a web query.

Hermes14 gravatar imageHermes14 ( 2014-03-21 10:58:43 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-03-20 19:19:39 +0200

Seen: 148 times

Last updated: Mar 21 '14