Hi Mangesh, I've read your other replies, and as to why you haven't received a formula method -- most people don't want to take the trouble on a volunteer basis to provide a more complex or difficult solution to a given problem when simple solutions exist. Also in business, the simplest solution is the best value. But I understand that in learning all aspects of a program, sometimes the exercise of solving a problem in various ways is useful.
I will provide a formula solution for you. Although it could be solved
several ways, I prefer using an array formula, since it can be written in
such a way that it can handle a string of text with any number of commas,
whereas non-array solutions would be limited to a set maximum. However,
even with the array solution, and even though the array will contain all of
the parts of the text string, you will only see on your spreadsheet as many
parts as there are cells in the area you place the array formula (there are
ways to use array formulas with "too large of arrays" to display on your
spreadsheet ,as part of other formulas, that are useful; for example
extracting certain values matching a condition, or summarizing the data (in
your data, you might want to know how many "Mumbai"s there are, how many
"32145"s, etc.), without actually displaying in multiple columns all the
split data.
. Select any cell on row 2 of your spreadsheet, then create the
following defined names using the Name Manager (formulas tab in Excel
2007+). I recommend making them Sheet-level names rather than the default
Workbook-level.
1. Name
=$A2
2. _Name.Terminated
=Name&","
3. _Name.ColumnIdx
=TRANSPOSE(ROW(INDIRECT("1:"&LEN(_Name.Terminated)-LEN(SUBSTITUTE(_Name.Term
inated,",","")))))
4. _Name.StartPositions
=IF(Name.ColumnIdx>1,FIND(CHAR(1),SUBSTITUTE(Name,",",CHAR(1),_Name.ColumnId
x-1)))+1
5. _Name.StopPositions
=FIND(CHAR(1),SUBSTITUTE(_Name.Terminated,",",CHAR(1),_Name.ColumnIdx))-1
6. Name.Columns
=MID(Name,_Name.StartPositions,_Name.StopPositions-_Name.StartPositions+1)
The purpose of using the defined names is to simplify the formula by
breaking it into steps. Insofar as the steps are referred to more than once
in the formula, it should make the formula faster to evaluate too.
Name.Columns is the culmination of our calculation and results in an array
containing each comma-separated value from column A, divided into separate
columns.
. To display the column-separated values on your spreadsheet, enter
the following formula in B2:
=Name.Columns
. Then highlight from B2 to the furthest column on row 2 needed to
display the maximum number of values ever expected. For a maximum of 3
values, highlight B2:D2, or for maximum of 10, B2:K2, etc.
. Then hit F2 (edit cell) and Ctrl-Shift-Enter (confirm array
formula). This confirms a multi-cell array formula over the selected cells.
. Copy the array-entered range from row 2 (e.g. B2:D2) to every other
row in your table.
Hope this is of interest,
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Mangesh Vimay
Sent: Monday, September 10, 2012 4:04 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Need help - To divide the string into three parts
Hi Friends,
I need your help to divide the string into three parts.
The description and sample is given below :
Inline image 1
Waiting for your response.
Thanks.
--
With regards,
MaNgEsH
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups
"MS EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
image001.png
Description: Binary data
