Today, lets learn how to create an interesting chart. This, called as network chart helps us visualize relationships between various people.
Demo of interactive network chart in Excel
First take a look at what we are trying to build.
Looks interesting? Then read on to learn how to create this.
Note: thanks to Hans whose email question inspired me to create this chart.
Tutorial to create interactive network chart in Excel
Note: This tutorial requires intermediate-to-advanced Excel knowledge. So if you are beginner, learn the basics & advanced concepts first and then comeback for this.
In order to create this chart in Excel, we need to first understand various ingredients of it.
As you can see, the chart contains these parts:
- A set of dots, each representing one stakeholder
- A set of grayish thick & dotted lines representing all relationships between people.
- A set of green thick & blue dotted lines representing relationships for the selected person.
- A slicer for person selection (can be replaced with list box or clickable cells in Excel 2007 or below)
- Summary statistics of the selected person
Getting started with the relationship data
To simplify our tutorial, lets assume we are talking about relationships between just 4 people, named Ash, Billy, Cynthia & Darren.
Our relationship matrix looks like this:
- 0 means no relationship
- 1 means weak relationship (for example: Ash & Billy just know each other)
- 2 means strong relationship (for example: Cynthia & Billy are friends)
The downloadable workbook is created to take up to 20 stakeholders.
Geometry of the network chart
If we draw the relationships between these 4 people (Ash, Billy, Cynthia & Darren) on a paper, it would look like this:
The 2 things we need to determine are,
- The location of dots (where person names are printed)
- The lines (starting & ending point of lines)
Plotting dots around circle
We need to plot our dots in such a way that gap between each dot is same. This will create a balanced chart.
What shape satisfies our need for such equal gaps? A circle of course.
Hey wait, I don’t see a circle in the chart you have shown…?
Thats right. We don’t need to draw a circle. We just need to plot dots around it.
- So we have 4 stakeholders, we need 4 dots
- If we have 12 stakeholders, we need 12 dots
- If we have 20, we need 20 dots.
Assuming the origin of our circle is (x,y), radius is r and theta is 360 divided by number of dots we need,
the first dot (x1,y1) on the circle will be at this position:
x1 = x + r*COS(theta)
y1 = y + r*SIN(theta)
[Related: How to create a spoke chart in Excel]
Once all the dots are calculated & plugged in to an XY chart (scatter plot), lets move on.
Plotting the lines
Lets say we have n people in the network. So that means, each person can have a maximum of n-1 relationships.
So the total possible lines in our chart are n*(n-1)/2
We need to divide it by 2 as if A knows B, then B knows A too. But we need to draw only 1 line.
My network chart template is set up to work with up to 20 people. So that means, the maximum number of lines we can have will be 190
Each line requires a separate series to be added to the chart. That means, we need to add 190 series of data just for 20 people. And that satisfies only one type of line (either dotted or thick). If we want different lines based on type of relationship, then we need to add another 190 series.
This is painful & ridiculous.
Fortunately there is a way out.
We can use far fewer series and still plot the same chart.
Lets say we have 4 people – A B C & D. For the sake of simplicity, lets assume the co-ordinates of these 4 are
- A – (0,0)
- B – (0,1)
- C – (1,1)
- D – (1,0)
And lets say, A has relationships with B, C & D.
That means we need to draw 3 lines, from A to B, A to C & A to D.
Now, instead of supplying 3 series for the chart, what if we supply one long series that looks like this:
(0,0), (0,1), (0,0), (1,1), (0,0), (1,0)
That means we are just drawing one long line from A to B to A to C to A to D. Agreed that it is not a straight line, but Excel scatter plots can draw any line as long as you provide a set of co-ordinates.
PS: This is a trick I learned from Roberto of E90E50. He used this trick in the winning entry of our recent dashboard contest.
See this illustration to understand the technique.
So instead of 190 series of data for the chart, we just need 20 series.
In the final chart, we actually have 40 + 2 + 1 series of data. This is because,
- 20 lines for weak relationships (dotted lines)
- 20 lines for strong relationships (thick lines)
- 1 line for highlighted person’s weak relationships
- 1 line for highlighted person’s strong relationships
- 1 set of no line & just dots for the people
How to generate all the 20 series of data:
This requires following logic:
- Assuming we need lines for the relationship of person n.
- That person’s dot location will be (Xn, Yn) and already calculated earlier (in the plotting dots around circle)
- We need total of 40 rows of data
- Every odd row will have (Xn, Yn)
- For every even row
- Divide the row number by 2 to get person number (say m)
- (Xn,Yn) if there is no relationship between n and m
- (Xm,Ym) if there is a relationship
We need MOD & INDEX formulas to express this logic in Excel.
Examine the download workbook to understand how its done.
Once all the line co-ordinates are calculated, add them to our scatter plot and format.
I used a macro to automate the formatting. It can be done manually too, just takes a little patience.
Slicer for selecting a person
This works only in Excel 2010 or above.
Select the first 2 columns of relationship matrix & create a pivot table.
Now, insert a slicer on Person name column.
Using simple IF formula, extract the selected person name from pivot table (examine download file for the logic).
And using the name, extract the subset of line data to separate range (2 sets of data – one for weak & one for strong relationships)
Add this new data to our scatter plot and format.
Format the slicer (using slicer styles) so that it looks slick.
Related: formatting slicers using styles.
NOTE About Slicers: If you change or add any data, you must refresh (from Data ribbon) to update the slicer. This can be automated with a macro, but I want to keep this file macro free.
[Alternative] Selecting a person with form controls
You can use either a list box or a range of clickable cells. See the 2003 compatible download file for an example of this.
Summary statistics
Using simple formulas extract statistics for the selected person and show them near the chart.
Adding labels to the chart (person names)
In our chart, we are showing person names instead of regular label like X or Y value. This is done with value from cells label feature in Excel 2013.
For earlier versions of Excel, I recommend using Rob Bovey’s excellent XY Chart Labels add-in.
Putting it all together
Once everything is ready, clean up the chart, slicer and other elements, put them together. And we are ready to go.
Download Network Relationships Interactive Chart Template
Click here to download the chart template workbook. The download is a ZIP file and it contains 3 workbooks – compatible with Excel 2013, 2010 & 2003+. Use the version that you need.
Please examine the formulas & chart settings to understand how it is constructed.
Note: Hit Refresh from Data ribbon to change slicer once you have added or modified data.
When to use network relationship chart?
A network graph is a good place to explore relationships between people in a project or team. It is especially useful when selecting a sub-set of people from large group to closely work on a project.
Any alternatives?
There is a popular Excel Add-in named NodeXL that can help you visualize and analyze relationships between people in a more in-depth fashion.
Check out Chord diagram & Cosmograph from E90E50 site for other ways to present this data.
Do you use these kind of charts?
I have used network charts earlier to depict relationships between various people or things. But I have never created such charts in Excel, I always used either Power Point or some other drawing program to create them. That is why I am excited about this chart. Figuring out the formula & graphing logic was fun.
What about you? Have you used such charts before? How do you like the network chart presented here? Please share your thoughts using comments.
32 Responses to “Extract Numbers from Text using Excel VBA [Video]”
Interesting that you are posting this at the same time as Doug http://yoursumbuddy.com/regex-function-sum-numbers-string/
Looks like two different articles about two different subjects, extracting numbers in text vs. summing all the numbers in text. Also, articles are published 20 days apart. Is the interesting part that there were two articles written about Visual Basic techniques within this month?
Sorry, that should have said 1 day, not 20. Was looking at the wrong thing. I still think it's just a nice coincidences to have multiple articles about VB written. Dick Kusleika also routinely writes about VB at dailydoseofexcel.com
What a lucky coincidence. I know about Doug's blog, but havent had a chance to read it in a while. Thanks for sharing the link.
I think that the best lesson that can come from the several salary survey solutions is that one should have anticipated the variety of monetary units. If the survey utilized drop down currency lists and limited the salary field to whole numbers only, etc. the resulting input would have been far cleaner. Sorry, Chandoo, but the messy input was, in my opinion, self-inflicted.
You are right. Since there are more than 200 different currencies, I thought a currency field would complicate the survey. The bigger problem was, Google Docs (which I used for survey) does not have an option to capture only numbers. Input fields were by text, so people entered in lots of different formats.
But I am happy how it turned out. It taught me several lessons on how to clean data.
Next time I will use a better tool to capture such responses.
Your post made me check how the "regular" and "irregular" decimal separators look like in different countries and it appears to be really interesting case. Take a look:
http://en.wikipedia.org/wiki/Decimal_mark
Cheers.
I am pretty sure you can replace this code block from your article...
If Text Like "*.*,*" Then
european = True
Else
european = False
End If
with this single line of code...
european = Format$(0, ".") = ","
Just to follow up on my previous post, I think I may have misunderstood the intent of your code. You were not looking to see if the computer system was using a dot for the decimal point, rather, you were looking to see if the Text was using a dot as the decimal point, weren't you? If so, then you could use this single line of code as to replace your If..Then..Else block...
european = Text Like "*.*,*"
But what if the number in Text was not large enough to display a thousands separator? Or what if it were a whole number? In either of those cases your original test, and my replacement for it, will fail. Maybe this would be a better test...
european = Right(Format$(Text, "."), 1) = ","
You are right. I am checking if the text has European format. And I loved your one line shortcut. I did not think of using LIKE in such context. Thanks for sharing that.
Again, you are right that this method would fail if the number is not big enough for a thousands separator. Since my data has annual salaries, all numbers are usually in thousands. So I did not think about it.
Hi ,
I have a question please. I'm working on a report that has alphanumeric on it and I only need to retrieve 7 integers that starts with 7 and 3 example SCM RIS PX RIS 02 - 7152349, ADSF\243434134, CM532345 and i need to get the 7152349. Can you please help me on this? I truly appreciate your help!
Thank you very much!
Hi-
The post was wonderful. Please take a look at this function also
Function ExtractNumber(InputString As String) As String
'Function evaluates an input string character by character
' and returns numeric only characters
'Declare counter variable
Dim i As Integer
'Reset input variable
ExtractNumber = ""
'Begin iteration; repeat for the length of the input string
For i = 1 To Len(InputString)
'Test current character for number
If IsNumeric(Mid(InputString, i, 1)) Then
'If number is found, add it to the output string
ExtractNumber = ExtractNumber & Mid(InputString, i, 1)
End If
Next i
End Function
Thank you so much. Your function code is amazing. It very useful for my lesson. Thank you so much.
To be more international.
At the beginning, for the rench format :
If fromThis.Value Like "*.*,*" Or fromThis.Value Like "* *,*" Then
european = True
End If
And at the end :
ElseIf ltr = "," And european And Len(retVal) > 0 Then
retVal = retVal & Application.DecimalSeparator
End If
Hi Chandoo,
Sorry, but your code does not work correctly with my Hungarian excel. My decimal separator is "," so
getNumber = CDbl(retVal)
will not convert the string to value, because you hard-coded "." as separator.
And, as you mentioned: "method would fail if the number is not big enough for a thousands separator" I would like to add: would fail if the user did not enter the thousand separator and also would fail if the thousand separator is not "," nor "." but " " (space chr) - as in Hungary.
This two functions could help to determine the system settings:
application.DecimalSeparator
application.ThousandsSeparator
Conclusion:
you say: "We do not need special treatment for regular format (61,000.30) as Excel & VBA are capable of dealing with these numbers by default." - it is true in case you system uses the regular format. 🙂
Cheers,
Kris
Awesome! It works !!
But how does one take into account negative numbers (say the list has negative numbers and I want to retain those negative numbers)
Thanks.
Hi. When I download this example, my excel is not showing formulas exactly. I wanted a ready version of this example, please. Thank you
Hi Chandoo,
Thanks for this brilliant article like many others that you have written for the benefit of many. Unfortunately, I am constantly having problems downloading your sample workbooks. I am currently using Excel 2007, and each time I try to download any of your sample workbooks, for e.g. the 'Extract Numbers Using VBA workbook', I get the following message 'This file is not in a recognizable format'.
I always get this message each time I try to download any of your sample workbooks. Please kindly advise me on how to resolve this.
Thank you.
Kenny
I have numbers like 12345-12-1 which I want to extract from text strings. 12345 might be variable there as 123, 1234, 12345, 123456,1234567 or so. When I get that in other cell (Column) I should see multiple entries of similar numbers with - (hyphen). How to do that?
@Madhav
Assuming your data is in cell A1
=LEFT(A1,FIND("-",A1)-1)
Thanks Hui for your response. Thank you for your time to find potential solution for my problem.
I tried your formula but was not successful in using the same.
here is more clarification so that you/others could help me.
Column A has following in Cells A1 to A4.. could be long..
ABCD 12345-12-1 XYZ 9878-02-9
LMNOPQ 12345-12-1 STQ 789748-98-5
NFHFKDJFKDS 123-23-1, NDKANSD
A FDSAFNDS 12345-12-1, ASNDSAND
from such data I need to extract the number with hyphens
remove , immediately after the numbers, separate the numbers with spaces
Column B shall look like:
12345-12-1 9878-02-9
12345-12-1 789748-98-5
123-23-1
2345-12-1
2 separate strings (numbers) having hyphen (-) therein should be separated with space.
@Madhev
Have a look at a solution using a simple UDF
https://www.dropbox.com/s/zexf4t9tmxmt3m9/Get_Numbers.xlsm?dl=1
Thanks Hui that worked well with the examples I provided.
I should have given following type of example:
2-ABCD 12345-12-1 X-2-YZ 9878-02-9
in the above case I do not want to extract a number and hyphen which is connected to or is part of text string..
Can you please help me modify the code to ignore numbers and - with text string.?
Thanks in advance.
@Madhav
So what is the answer expected from
2-ABCD 12345-12-1 X-2-YZ 9878-02-9
Thanks for your interest and time Hui.
so when I have text like
2-ABCD 12345-12-1 X-2-YZ 9878-02-9 3-abc-4-efg in Cell A2
in B2 the answer should be only numbers with hyphens and no text with numbers or hyphens
12345-12-1 9878-02-9 OR
12345-12-1 some delimiter (, or 😉 9878-02-9
The logic I thought was (but unable to do)
1. remove all strings containing text (and - and numbers) and then extract only numbers containing hyphens
2. Extract numbers in only following format ( # is a digit below) and ignore numbers and hyphens in any other format
#######-##-#
######-##-#
#####-##-#
####-##-#
###-##-#
##-##-#
Hope this helps.
Why not just use the function =getNumber ?
=getnumber doesn't extract numbers with hyphens..
also need to ignore numbers and hyphens associated with text string
When I use this code that code give me error
cdb1 is not highlight can u explain me
@Deepak
It runs fine for me
Select the first line and Press F9 to set a stop point
goto a cell and edit the function and press Enter
Then you can step through the code when it runs using F8
report back what happens
HI,
How can we add spaces between numbers and removing decimals.
how can we make spaces in the reesult e.g 25 655 2335
Dear Team,
I need to extract number (cheque number) from a cell (some numbers may repeat that to be ignored),
Text is - :-Inward Clg Cheque 00992924 00992924,BD
Result should be - 992924
Kindly help in getting formula for this (please email the code or VBA Code)