Split column by last character without any delimiter where strings varies in length

**kakaNit** · 12-27-2023, 09:45 AM

Hi,
I would like to split a column by the last character. These strings doesn't contain any delimiters and varies in length. The last character should be subtracted from the first column. Like this:

If A1 contains the word "BAKERY", I would like A1 to be left with "BAKER" and B1 with "Y".

I'm aware that this kind of formula could create a circular reference. Is my best shot to use two columns with the the formula (in B1)

=LEFT(A1, LEN(A1) - 1)

and (in C1)

=RIGHT(A1, 1)

or could this be arranged with a dynamic array function somehow?

**AliGW** · 12-27-2023, 09:54 AM

You cannot change the contents of a cell with a formula - that would require VBA.

Try this in B1:

=LET(b,LEFT(A1,LEN(A1)-1),c,RIGHT(A1,1),HSTACK(b,c))

This would do the whole column (up to row 1000) at once:

=IFERROR(LET(b,LEFT(A1:A1000,LEN(A1:A1000)-1),c,RIGHT(A1:A1000,1),HSTACK(b,c)),"")

**kakaNit** · 12-27-2023, 10:01 AM

I understand, just as I feared.
Thank you, this formula was really neat and it works just as I hoped.

**AliGW** · 12-27-2023, 10:03 AM

Did you see the second formula?

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

**kakaNit** · 12-27-2023, 10:05 AM

I did, thank you AliGW!

**Glenn Kennedy** · 12-27-2023, 01:11 PM

The second one is not so useful, as you wind up with a pile of blanks in your array. This is better:

=LET(a,TOCOL(A1:A1000,1),b,LEFT(a,LEN(a)-1),c,RIGHT(a,1),HSTACK(b,c))

Split column by last character without any delimiter where strings varies in length

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Split column by last character without any delimiter where strings varies in length

Re: Split column by last character without any delimiter where strings varies in length

Re: Split column by last character without any delimiter where strings varies in length

Re: Split column by last character without any delimiter where strings varies in length

Re: Split column by last character without any delimiter where strings varies in length

Re: Split column by last character without any delimiter where strings varies in length

Thread Information

Users Browsing this Thread

Similar Threads

Split Column by delimiter

[SOLVED] Split a column using comma delimiter

How to split a column at first delimiter only

[SOLVED] Split specific chars in one column to other columns with no delimiter

[SOLVED] 2010: random length strings in cell - need to split

[SOLVED] Split One Column at Delimiter While Copying Contents of Second Column to Populate New Rows

Find character when the length varies

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions