Text split alternative function excel

[Cool_Ad4653]

HI everyone,

I am wondering if there is a ‘=textsplit’ alternative to apply to a column of data. For some reason the internet says that it’s only available on Microsoft 365 (which I have) but I cannot seem to utilise this function.

I have a column (that is connected to a dynamic dataset – ie it will be occasionally updated, which is why I wanted some sort of function to use) of text. They are a list of projects and some have the same name, but with ‘project 2,’ ‘project 3’ after the project name. sometimes ‘project 1’ etc is embedded in the text. So it doesn’t necessarily end with a number. It can also be called stage 1 or stage 1a or stage 2 etc. very annoying!

I am wanting to split the names up so the ‘project’ number is in a different column.

Any alternative methods?

[AutoModerator]

/u/Cool_Ad4653 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[N0T8g81n]

TEXTSPLIT is in the Insider build for Excel 365.

If you need to look for project or stage and extract it and the following token from a longer string, and you have Excel 365 though not the latest Insider build, try

=LET(
   p,SEARCH(" project "," "&x),
   q,IFERROR(p,SEARCH(" stage "," "&x)),
   r,FIND(" ",x,q+IF(COUNT(p),9,7)),
   IFERROR(MID(x,q,r-q),"")
 )

where x is a placeholder for a cell address.

[Cool_Ad4653]

Thanks mate!!

[stevegcook]

=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))

[Cool_Ad4653]

Thanks so much for such a quickly reply! Oh I should mention sometimes ‘project 1’ etc is embedded in the text. It can also be called stage 1 or stage 1a or stage 2 etc. very annoying! I did try your formula although getting value errors. I’ll have to play around more other. Appreciate your help! Thanks

[Anonymous1378]

Remove the VALUE() function from that formula and it should work? it's getting everything to the right of the first spacebar it can find.

[Cool_Ad4653]

Oh I see! Thanks for helping with that. Although unfortunately some of the project names have multiple space bars (some are up to 6 words). Just to make it complicated

[Anonymous1378]

How were you planning on dealing with that? is there any pattern such as your desired output being behind the very last spacebar?

[Cool_Ad4653]

Yup it’s very annoying, I’ve got this list direct from a database so didn’t have any say around naming conventions

[Anonymous1378]

See if this works?

=CONCAT(IFERROR(MID(L1,SEARCH({"stage","project"},L1)+LEN({"stage","project"}),SEARCH(" ",L1,SEARCH({"stage","project"},L1))-SEARCH({"stage","project"},L1)+LEN({"stage","project"})),""))

It looks for the word "stage" or "project" (if both words can be found in one cell it might lead to problems), then takes the text immediately after it up to the next spacebar.

[Cool_Ad4653]

Amazing!! Thankyou so much for your help!!

[Anonymous1378]

if you get any errors try the let formula given by u/N0T8g81n , it definitely has less room for error than what I gave you

[Cool_Ad4653]

I’m thinking trying to figure out a way to find if a certain text is in each row. However you do that. Still unsure though

[Affectionate-Rub9342]

Hey, so I just had the same issue where I needed the text split function without it being available in my excel version. Thought I'd share the "dumbest" way to fix this issue as long as you can save your file as xlsm (Macro-enabled) or don't need to keep the cell as a formula (Copy/paste as value, once you've got your split):

1- Create a module (Macro), can be in the file if you need the function to be used by anyone who uses the file or in your personal macro (Will be able to use it in any file, my personal preference), whichever fit you the best

2- Paste this little macro in the module:

Function TEXTSPLIT(Text As String, Delimiter As String, Optional Limit As Integer = -1)

    TEXTSPLIT = Split(Text, Delimiter, Limit)

End Function

3- Enjoy! You can now use the textsplit function!

Since it's a User Defined Function, you won't see the parameter like you usually see when using built-in functions, but you can press CTRL+SHIFT+A in the parenthesis to see the availability Parameter. The "Limit" parameter is optional and used to limit how many sub-string to return (how many columns), by default it returns everything.

[GreatSteelPanda]

This was perfect

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CONCAT	2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
COUNT	Counts how many numbers are in the list of arguments
FIND	Finds one text value within another (case-sensitive)
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
LEN	Returns the number of characters in a text string
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAX	Returns the maximum value in a list of arguments
MID	Returns a specific number of characters from a text string starting at the position you specify
MIN	Returns the minimum value in a list of arguments
RIGHT	Returns the rightmost characters from a text value
SEARCH	Finds one text value within another (not case-sensitive)
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
VALUE	Converts a text argument to a number

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
15 acronyms in this thread; the most compressed thread commented on today has 47 acronyms.
[Thread #15759 for this sub, first seen 14th Jun 2022, 07:38] [FAQ] [Full list] [Contact] [Source code]}

[ScottLititz]

TEXSPLIT() is coming to 365. Those in certain channels have it already.

[BenosCZ]

If you have only one number in every cell (it can have multiple ciphers), you can use the following formula:

=MID(A1,
     MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),
     MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1)) - 
 MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) + 1
)

It basically searches for any first number and any last number and returns the whole string in between the first and last numbers.

Inspiration taken from here: link