Help

Delete first matching characters in a column

Topic Labels: Formulas
Solved
Jump to Solution
2739 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Stephen
6 - Interface Innovator
6 - Interface Innovator

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

 

2 Solutions

Accepted Solutions
Alexey_Gusev
13 - Mars
13 - Mars

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}), '')
)

 

 


See Solution in Thread

Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

The formula for the "Actual Company Name" field is

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

Screen Shot 2023-01-27 at 10.14.24 AM.png

See Solution in Thread

4 Replies 4
Alexey_Gusev
13 - Mars
13 - Mars

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}), '')
)

 

 


Ron_Daniel
8 - Airtable Astronomer
8 - Airtable Astronomer

The formula for the "Actual Company Name" field is

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

Screen Shot 2023-01-27 at 10.14.24 AM.png

Many thanks @Alexey_Gusev ! Amazing worked a treat. 

 

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