This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Formula to Extract letters only from String

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

1
2460
6

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 03, 2019 04:03 AM

I have afield with inventory numbers. There numbers start with a bin letter designator and then a number. ex, A2, G22, AA15, AB21

I was using the LEFT function to grab the First character to identify the bin letter, but now that I have grown and now have bins AA, AG, etc. How to I have have Airtable grab just the letters out of the field or grab two letters is the inventory has one?

Thanks,

Solved! Go to Solution.

Reply

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 03, 2019 02:31 PM

Thanks @Bill.French - very kind but actually I misread the question! Although of course your suggestion that this allow you to find the length is clearly the way forward.

However, there is one issue and that is that the LEN() function doesn’t work on a number - so you need to convert the VALUE(Bin) (I’ve used a field called Bin for the whole Inventory Number) to a string by adding a Zero length string ("") to it. The formula then ends up as:

Turn’s out it wasn’t so obvious but hopefully this should be the answer.

Reply

6 Replies 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 03, 2019 05:39 AM

The simplest way to do this is to use the VALUE() function which will extract the numeeric part from the inventory number.

Hope this helps…

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 03, 2019 10:37 AM

How can I use this to extract the BIN Letter?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 03, 2019 02:07 PM

I think what @Julian_Kirkness is intimating is that using Value() to know the numbers in the string, you are now in a position to know the length of the number portion as well as the length of the entire string and by deduction, the length of the letters portion.

You see where this is going? :winking_face:

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 03, 2019 02:31 PM

Thanks @Bill.French - very kind but actually I misread the question! Although of course your suggestion that this allow you to find the length is clearly the way forward.

However, there is one issue and that is that the LEN() function doesn’t work on a number - so you need to convert the VALUE(Bin) (I’ve used a field called Bin for the whole Inventory Number) to a string by adding a Zero length string ("") to it. The formula then ends up as:

Turn’s out it wasn’t so obvious but hopefully this should be the answer.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 03, 2019 02:46 PM

Perfect, thank you very much

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 03, 2019 03:34 PM

Reply