Text to rows formula only able to separate 66 values

Gavinmk

New Member
Joined
Jan 6, 2016
Messages
13
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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)
 

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
326
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
 
Last edited:

FlameRetired

Active Member
Joined
Feb 19, 2016
Messages
326
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
Code:
=";"&SUBSTITUTE($A$2,"; ",";")
Then in C2
Code:
=LEN($B$2)

Then this array entered formula in D2 filled down until you get blanks.
Code:
=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?
 

Forum statistics

Threads
1,136,302
Messages
5,674,965
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top