Skip to main content
Solved

Delete first matching characters in a column

S

Hi all

Is there a way to delete the certain matching characters that might appear in a column? For example I have a column of data (800+ cells) of company names, and 90% of them are prefixed with a 'number' (that run between 1 and 9), followed by a 'period', followed by a 'space'. For example "1. Some Company Name" or "8. Another Company Name".

10% of the data in the column may not have any numbers or spaces, OR some of the company names may not have a 'space' after the 'period' for example "7.Yet Another Company Name" or "And Another Company Name".Amy advice would be greatly appreciated.

Stephen

 

Best answer by Alexey_Gusev

Hi,

Use SUBSTITUTE to remove some certain characters pattern

Use REPLACE to remove characters at some certain place in string

In your case, you need to remove 'from start (position number 1) to period (located by FIND)'

 

REPLACE({field}, 1, FIND('.',{field}), '')

 

to remove possible space after period, wrap it all in TRIM:

 

TRIM( REPLACE({field}, 1, FIND('.',{field}), '') )

 

 

View original
Did this topic help you find an answer to your question?

4 replies

Alexey_Gusev
Forum|alt.badge.img+12
  • Alexey_Gusev
  • Brainy
  • 1116 replies
  • Answer
  • January 27, 2023

Hi,

Use SUBSTITUTE to remove some certain characters pattern

Use REPLACE to remove characters at some certain place in string

In your case, you need to remove 'from start (position number 1) to period (located by FIND)'

 

REPLACE({field}, 1, FIND('.',{field}), '')

 

to remove possible space after period, wrap it all in TRIM:

 

TRIM( REPLACE({field}, 1, FIND('.',{field}), '') )

 

 

S
1 person likes this
  • S
    Stephen11
Ron_Daniel
  • Ron_Daniel
  • Inspiring
  • 104 replies
  • January 27, 2023

The formula for the "Actual Company Name" field is

REGEX_REPLACE({Listed Company Name},"[0-9](. )|[0-9](.)","")

S
Alexey_Gusev
2 people like this
  • S
    Stephen11
  • Alexey_Gusev
    Alexey_Gusev
S
  • Stephen11
  • Author
  • Known Participant
  • 23 replies
  • January 27, 2023
Alexey_Gusev wrote:

Hi,

Use SUBSTITUTE to remove some certain characters pattern

Use REPLACE to remove characters at some certain place in string

In your case, you need to remove 'from start (position number 1) to period (located by FIND)'

 

REPLACE({field}, 1, FIND('.',{field}), '')

 

to remove possible space after period, wrap it all in TRIM:

 

TRIM( REPLACE({field}, 1, FIND('.',{field}), '') )

 

 


Many thanks @Alexey_Gusev ! Amazing worked a treat. 

 

S
  • Stephen11
  • Author
  • Known Participant
  • 23 replies
  • January 27, 2023
Ron_Daniel wrote:

The formula for the "Actual Company Name" field is

REGEX_REPLACE({Listed Company Name},"[0-9](. )|[0-9](.)","")


Brilliant @Ron_Daniel. A nice elegant solution. 'Many' thanks.

Ron_Daniel
1 person likes this
  • Ron_Daniel
    Ron_Daniel

Reply


Most helpful members this week

Terms & Conditions