Split function Issue

Question

I have a rather simple question however I cannot seem to find the issue.

I am using the below Split function, however, when I have an account number leading with 0 it automatically deletes the zero as it is interpreted as a number, not as text, however I have tried implementing to text function but as account number vary in sizes and not standard then I might have some accounts with more 0 than it supposed to have. I have also tried other ways however nothing seems to work.

Does anyone have a solution for this?

Code using:

=SPLIT(A2, "Account Number: ")

player0 · Accepted Answer · 2019-09-06 10:11:51Z

1

you will need to use regex:

=REGEXEXTRACT(A1, "Account Number: (.*)")

or:

=SUBSTITUTE(SPLIT(A1&"♥", "Account Number: "), "♥", )

edited Sep 6, 2019 at 10:11

answered Sep 6, 2019 at 7:03

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alan Saydon Over a year ago

Thanks, they both worked perfectly, could you please tell me what the (.*) in the =REGEXEXTRACT is and also the "♥" in the =SUBSTITUTE Function do exactly?

player0 Over a year ago

(.*) - means "everything". the ♥ symbol is just unique symbol - it has no significance eg can be anything ♂♀♫☼►♣♠♦♪.... the reason is to prevent converting numbers to numeric which SPLIT does. if you add some text/symbol to it its treated as Plain text/string instead of numeric value

Alan Saydon Over a year ago

Nice one Thanks for explaining these!

Collectives™ on Stack Overflow

Split function Issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related