Can this UDF changed to Rows instead of Colomns

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows
Hi guys,

To break-down addresses this is the UDF I have been using, Can someone be kind enough to change its feature to break it down to rows instead of breaking it down to Colomns?

Thanks a lot,

Code:
Function SplitValues(a As String, b As String)
  SplitValues = Split(b & String(Application.Caller.Count, a), a)
End Function
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,357
Office Version
  1. 2013
Platform
  1. Windows
Please_H,

Try....
Code:
Function SplitValues(a As String, b As String)  
SplitValues = WorksheetFunction.Transpose(Split(b & String(Application.Caller.Count, a), a))
End Function

Hope that helps.
 
Last edited:

AkaTrouble

Well-known Member
Joined
Dec 17, 2014
Messages
1,544
don't think my question is technically hijaking thread lol

anyone care to explain what these functions are doing (i get that they are splitting data in some way) I cant see input and result logic from the code
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

don't think my question is technically hijaking thread lol

anyone care to explain what these functions

:LOL: , Even though I too have no clue how the Macro works, I understand that Mr. Snakehips used the transpose function here in helping me out.... :)
 

AkaTrouble

Well-known Member
Joined
Dec 17, 2014
Messages
1,544
:LOL: , Even though I too have no clue how the Macro works, I understand that Mr. Snakehips used the transpose function here in helping me out.... :)

thanks for reply,

yeah i understand the functions and the change it was i could not grasp the input string and what the output would be

i was more wanting to see before after example maybe to help see if i could re-use the function in any of my projects

i just cant see from the function code what is being input and output if i try the code i can only get an error or a 0 to return
 

Please_H

Board Regular
Joined
Apr 16, 2017
Messages
181
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

thanks for reply,

yeah i understand the functions and the change it was i could not grasp the input string and what the output would be

You're most Welcome... I required this to get an address sorted...Ex : No. 55/3, Rabbit Lane, Fox Street, Cow City, Buffalo State, New York,100200, USA

My issue was I wanted to Break down the address from its delimiter "," to cells...

The UDF I posted would give me something like this,

The Result :

A1 = No. 55/3
B1 = Rabbit Lane
C1 = Fox Street
D1 = Cow City
E1 = Buffalo State
F1 = New York
G1 =100200
H1 = USA

Now I wanted this in rows, not in colomns...because I am creating a module for myself and the rest of the staff, so I wanted to make things easier by getting the breakup to rows instead the colomns...

So Mr.Snakehips helped me on that, Now the result would be :

A1 = No. 55/3
A2 = Rabbit Lane
A3 = Fox Street
A4 = Cow City
A5 = Buffalo State
A6 = New York
A7 =100200
A8 = USA

The UDF is an array Formula, therefore you have to first select how many cells you want the breakup for, and then while you have already selected the cells hit F2, then the Formula needs to be entered as =Splitvalues(",",B1) (Edit Note : After entering the Formula hitting the enter alone wouldn't work, therefore hit ctrl+shift+enter)

Considering the Complete address is in cell B1

In case you select more cells than the required, after the Split the rest of the Cells would be just blank...But how these additional cells would be helpful is, in getting it filled in considering you get longer addresses...

Hope this Helps..

Tc.

:)
 
Last edited:

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,357
Office Version
  1. 2013
Platform
  1. Windows
Sorry for the delay in offering some sort of explanation.

Split is a visual basic function that uses a given delimiter within a string to string it into substrings. The substrings are returned to a one dimensional, zero based array.

Forget the actual variable arguments that appear in P_H's example as they are meaningless outside of the code that is calling his SplitValues function.

If for example the string to be split is "one-two-three" and the delimiter is the "-" and the array to hold the substrings is SArry
Then the code will be
Code:
  SArry = Split("one-two-three", "-")

The result would be SArry having three elements equating to {"one", "two", "three"} which can be directly assigned to a single row range such as A1:C1, "one" "two" "three"
If assigned to A single column range e.g. A1:A3 then all three cells would receive "one", the array's first element.

Using the Transpose function on SArry it becomes three row elements as in {"one"; "two";"three"} (note semi-colons not commas) with can be assigned to a single column range e.g. A1:A3 as "one" "two" "three"
Similarly if that array were assigned to a single row range then each column would only get the value "one"

Hope that makes sense?
 
Last edited:

AkaTrouble

Well-known Member
Joined
Dec 17, 2014
Messages
1,544
Sorry for the delay in offering some sort of explanation.

no apologies needed however Thanks very much for your reply it helped a lot

one more question if i may having parsed the string into an array i understand the A1:C1 range output for the array and the transpose to convert to a horizontal array

how would one be abble to use the array and define each element to output to seperate cell for example say i wanted to put just the "two" (element 2 of array) into a cell

thank you both for your time and explanations
 

Forum statistics

Threads
1,141,293
Messages
5,705,532
Members
421,399
Latest member
hjweiss00

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