Text to rows formula only able to separate 66 values

[Gavinmk]

For some reason, if I include more than 66 of the below countries, it says #VALUE! in all of the cells that have the formula I'm trying to use.


I am trying to use this formula: =TRIM(MID(SUBSTITUTE(E$2,";",REPT(" ",500)),ROW(A1)*500-499,500))


I am trying to enter in this as the input:
Albania; Algeria; Angola; Argentina; Armenia; Australia; Austria; Azerbaijan; Bahamas; Bahrain; Bangladesh; Barbados; Belarus; Belgium; Belize; Bermuda; Bolivia; Bosnia & Herzegovina; Brazil; Brunei; Bulgaria; Cameroon; Canada; Cape Verde; Cayman Islands; Chile; Colombia; Costa Rica; Côte d'Ivoire; Croatia; Curacao; Cyprus; Czech Republic; Denmark; Dominican Republic; Ecuador; Egypt; El Salvador; Estonia; Faroe Islands; Fiji; Finland; France; Georgia; Germany; Ghana; Greece; Guatemala; Honduras; Hong Kong; Hungary; Iceland; India; Indonesia; Iraq; Ireland; Israel; Italy; Jamaica; Japan; Jordan; Kazakhstan; Kenya; Korea; Kuwait; Kyrgyzstan; Latvia; Lebanon; Libya; Liechtenstein; Lithuania; Luxembourg; Macau SAR; Macedonia; Malaysia; Malta; Mauritius; Mexico; Moldova; Monaco; Mongolia; Montenegro; Morocco; Netherlands; New Zealand; Nicaragua; Nigeria; Norway; Oman; Pakistan; Palestinian Territories; Panama; Paraguay; Peru; Philippines; Poland; Portugal; Puerto Rico; Qatar; Romania; Russia; Rwanda; Saudi Arabia; Senegal; Serbia; Singapore; Slovakia; Slovenia; South Africa; Spain; Sri Lanka; St. Kitts and Nevis; Sweden; Switzerland; Taiwan; Thailand; Trinidad & Tobago; Tunisia; Turkey; Turkmenistan; U.S. Virgin Islands; Ukraine; United Arab Emirates; United Kingdom; United States; Uruguay; Uzbekistan; Venezuela; Vietnam; Zimbabwe




Here's the workbook I'm using: country name verifier.xlsx - Speedy Share - upload your files here

 

[FDibbins]

I can get it to 81 names (up to Mongolia), using this...
=TRIM(MID(SUBSTITUTE($A$8,";",REPT(" ",LEN($A$8))),LEN($A$8)*(ROW()-$F$1)+1,LEN($A$8)))

(your formula seems to pill in a random I word between Iran and Ireland)

 

[Gavinmk]

Is there a better way of doing this?

 

[FlameRetired]

Gavinmk,

I have encountered this before. There is a character limit that all those spaces can exceed. I got #VALUE! errors when I did that, too. Also when parsing such a long string there is a tendency to "chop" the words like I suspect Ford encountered.

What is the end goal? Are you trying to separate these countries into different cells or are you trying to put them into an array? Other?

I'll try to find how this finally got resolved. The solution might apply. Can you tell us more?

Dave

Edit Never mind. The title tells me. Duh. LOL

 

[FlameRetired]

Gavinmk,

I found that old file.

This uses two helper cells to reduce calculations in the last formula.

With the original string in A2 this helper formula in B2

=";"&SUBSTITUTE($A$2,"; ",";")

Then in C2

=LEN($B$2)

Then this array entered formula in D2 filled down until you get blanks.

=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($B$2,SMALL(IF(MID($B$2,ROW(INDIRECT("1:"&$C$2)),1)=";",ROW(INDIRECT("1:"&$C$2))+1),ROWS($2:2)),50),";",REPT(" ",50),1),50)),"")

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Here is the first few rows of what it does. I did not include A2 or B2 below. The strings are too long.

Row\Col	C​	D​
1​	Helper Len
2​	1219​	Albania
3​		Algeria
4​		Angola
5​		Argentina
6​		Armenia
7​		Australia
8​		Austria
9​		Azerbaijan
10​		Bahamas
11​		Bahrain
12​		Bangladesh
13​		Barbados
14​		Belarus
15​		Belgium
16​		Belize
17​		Bermuda
18​		Bolivia
19​		Bosnia & Herzegovina
20​		Brazil
21​		Brunei
22​		Bulgaria
23​		Cameroon
24​		Canada
25​		Cape Verde
26​		Cayman Islands
27​		Chile
28​		Colombia
29​		Costa Rica
30​		Côte d'Ivoire
31​		Croatia
32​		Curacao
33​		Cyprus

Does this do what you want?