Close Window
Free Ebook
Get Your Free Excel
Top 15 Excel Tutorials from
Instant Access!

Making Numeric And Alphabet Value Separate


Is there any formula to extracting numeric and alphabet value separate from a string ?

I dont Want to use Regex.substitute



Free Excel Courses

Similar Excel Tutorials

Resizing rows and columns in Excel is an easy process. Simply left click in between the columns and drag the mouse ...

You can change the size of the units on a chart axis, their interval, where they start, where they finish, and mor ...

In this tutorial I am going to show you how to combine multiple chart types to create a single chart in Excel. This ...

How to use a SUMIF or SUMIFS function (or any conditional function or formula) on cell background color. There is n ...

Helpful Excel Macros

- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra

- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a

- This macro will print all of the embedded charts which are on the current active worksheet. Each chart will be printed o

- This macro will print every embedded chart in the active workbook. If you have an embedded chart in any worksheet within

Similar Topics

Hello All

I wonder if you could help with this one, I have a list of data like this (no spaces or other characters)


What I need to do is split each of the characters in to its own cell

if JN551122B was in cell A1 I'd want to return:

B2 C2 D2 E2 F2 G2 H2 I2 J2
J N 5 5 1 1 2 2 B

The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc.

Thanks for your help!

I have AplphaNumeric part numbers that sometimes contain hyphens or decimals. In order to make them more searchable (since people don't always use the hyphens or decimals properly when they search) I would like to create all the possible variants of the part number, but with one formula.

So AHW18.787 becomes AHW18 787 and AHW18787 with

=SUBSTITUTE(A1,"."," ") and
=SUBSTITUTE(A1,".","") respectively, and

AHRTW-A18-7007 becomes AHRTW A18 7007 and AHRTWA187007 with

=SUBSTITUTE(A1,"-"," ") and

What I need to be able to do is merge these formulas into one, so that no matter what format the part number is in A1, I get a version of it with spaces in B1, and a version of it with no spaces in C1. I just have not been able to nest it all together - is it even possible with this command?


I'm trying to separate bars inside a bar chart into separate groups. Adjust the gap applies to all bars. How will I be able to achieve what I need? Please help and thanks

I have a textbox in my userform where anything typed in the box is entered into cell A2 in my worksheet. However, multiple lines of text are not properly displayed in my worksheet. I have enabled MultiLine and EnterKeyBehaviour in the properties window of the textbox so I can type multiple lines in my textbox. The problem is that when I press the button to enter the text from my txtbox to cell A2, the cell doesn't actually display the text in separate lines. It just places a square symbol in the place that enter should have been pressed and the text should have been split into separate lines.

How can I get the separate lines of text in my textbox to be properly displayed in a cell? Thanks for your help in advance..

Ok, I'm going to try and explain this. I am going to have 5 data points in cells such as A2:A6

All I need is to know if there is an upward trend or downward trend of the numbers.... I can't have something that simply takes the first and last number and checks if it's higher or lower. I need to actually see if they are trending upwards. Also how would i go about making it so that if they are trending upwards it shows an up arrow, and down arrow for downwards...

And finally, if the trend continues at the same rate, how long it will take the number to reach a goal. Each data point will be 1 week apart, with an ultimate goal needed to be achieved at the end.
This will be a completely separate formula on the sheet....

Any help?

Is it possible to assign values to names in a list, so that when you validate
it as a drop-down list, you can select a name from the drop-down and it's
corresponding value will be added to separate cell? Basically, I have survey
questions which have five possible responses: Excellent - Very Good - Good -
Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for Very
Good and so on, so that when a response is selected from a drop-down, it's
value appears in a separate cell (so that we can calculate a total and
average score from the selections).



My Cell C3 is a numeric value.....I have set conditional formatting to
make the text red and bold when the number is equal or less than 10,000
is there a way I can make cell E3 display a message when the C3
condition is true?? or if not a message is there a way to make a
message box pop up when my C3 condition of less then or equal to 10,000
is true?

how would I do something like this?


I am looking for a formula to remove special characters and spaces from a cell

I have been using "substitute" but this requires me to know which character I want to remove and this isn't always known

I have tried looking at some macro solutions but became lost quite quickly

Any help would be much appreciated


I see a formula that our Human Resources Department is using to annualize voluntary turnover.

It looks like this:


So if I substitute with some actual data:

=(6/((22+33)/2))*12/months the answer becomes 21.8% (as the annualized voluntary turnover rate).

What does the *12/months actually do mathematically? I cannot find it in the excel help files and have not been able to figure this out using math (on the calculator).

I am a new user and trying to setup a database in excel. I can select all addresses and copy but when trying to paste into excel it does so in 1 cell.

Is there a way I can paste the separate email id's into individual cells so I don't have to do it individually?

Many thanks

I'm trying to have separate column widths in the same column, based on the rows.

the top half of my spreadsheet are assumptions, formatted with the corrects widths needed, but then below I have calculations and such that I do not want the same widths for. how can I lock in the top part so I can decrease the column widths for the bottom half of my spreadsheet?

hope this makes sense.

I have and excel sheet that was a contact list apparently exported wrong and has the names and addresses all together in each cell in about 350 rows in column A, they are all in this format

john doe email address here

for some reason the email address in brackets won't show up here but each email address is in brackets

I need to get the name in one column and the email address without the brackets into another, any ideas?

Hi, hope someone can assist, i want to add some arrow syimbols in to a formula, so that when the formula works out it shows an up arrow for higher or a down arrow for lower, i am using this formula at the moment because i dont know any better but it works, putting in the word up or down:

I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?


Sale = A1
Cost = B1
Margin = C1

I know to calculate Margin its' C1=1-(B1/A1) and to calculate Sale its, A1=B1/(1-C1), and I can do that in separate columns. But...... I'm tasked with trying to find a way so that I can do it in three columns. As in if I change cost then margin recalculates or if I change margin then sale recalculates. I'm thinking that this is going to cause a circular error but my boss says there's got to be a way to do it, figure it out. Any suggestions?


Hi There

Im trying to Sum the total of a number of cells using the following formula:

however some of those cells might be blanks as they are dependant on other data. The probelm im having is that when they are blank i am getting the the sum as 0. i know this isnt the case but dont know how to rectify my code.

Here's my formula... =SUM(S7)-T5

If that number is less than -100, I want it to show as blank.

Any help?

Right now, I'm trying to accomplish this by making a conditional format...meaning when my cell equals less than -100 I make the cell color and font the same (so it looks blank, but its not). When I print it, it still shows the negative value.


First time in this forum. Hi all. I have a challenging question, is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?

DATA output should be
asd67,h876 --------> 67876
2,3,ujdj5&34 -------> 23534
909k86m34 --------> 9098634

Hope this makes sense?

Hi all,

I have some formulae in A1, is it possible for cell B1 to display the value only of A1 without the link to the formulae. I dont want to have to run a macro, or paste values, more of a formula that could be assigned to cell B1 so whenever A1 is changed, so is B1 automatically.



Hi there, I have a string of numbers and I would like to add zeros to the end of it.

For example my numbers are formatted like this: 1234 and I would like to add zeros to the end so it looks like this: 123400 (no decimal).

I would do it manually but I have a column of over 2000 different numbers.

Thanks for your help, you guys are great!


I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.

I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc.

When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result.

I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before.

Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.

There's about 50+ cells that need referencing and I got to get this done for work.

Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time.

I am looking for a method/formula that will reverse multiple text entries from "abcde" to "edcba". The entries are composed of several words that need to be reversed ie from "ab cde fg" to "gf edc ba"



As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).


Sub AddImage()
Dim strPath As String
Dim strFile As String
Dim Picture As Object
Dim Response As String
Dim C As Range
strPath = "C:\\Photos\"
For Each C In Range("A1", Range("A65536").End(xlUp))
 If C 0 Then
 With C.Offset(0, 1)
 Set Picture = Nothing
 On Error Resume Next
 Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
 Picture.Top = .Top
 Picture.Left = .Left
 Picture.LockAspectRatio = msoTrue
 Picture.Width = 157
 Picture.Height = 138

 End With
 End If
Next C
End Sub

I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.

Please help


I want to create 6 numbers from 11 numbers..
lets suppose my numbers a 1,2,3,4,5,6,7,8,9,10,11
I want to generate all possibilities of the 6 numbers. But i dont want duplicate series.. I mean 1,2,3,4,5,6 and 6,5,4,3,2,1 are duplicates.


Hi there. I know it sound like a really simple thing but its really stumped me.

Instead of the y axis being on the left hand side, i wish for it to cut at 0,0. I have positive and negative values and I need it to be in the centre instead of on the left.

I have tried formatting both the axis and it would appear that the x axis already cuts the y axis in the right posistion so i need to format the x axis to make the y axis cut it at 0,0. I have already tried typing in 0 instead of 1 and it keeps saying it need to be number more than or equal to 1.

Any help would be greatly appreciated.


Morning all,

Say I had a line graph for 4 years and I wanted it to be a solid line for the first three and a dashed line for the last one, is that possible?

Many thanks for your help

Related pages

intercept function excelsetting up amortization schedule excelhow to protect worksheet in excel 2007excel subscript out of rangevba ascii codeun concatenate in excel3706 provider cannot be foundspeedometer chart excelrisk matrix excelexcel formula less than or equal tomargin calculator excelexcel formula sort alphabeticallyhow to insert percentage formula in excel 2007attachmate extra extremestop macro vbainstall analysis toolpakvba runtime error 1004 select method of range class failedhow to disable macros in excel 2010excel subgroupstacked scatter plot excelcalculating a percentage in exceladding calendar to excelsample timesheet exceliserror in excelcrystal reports convert number to stringsort macro excelexcel ltrimvba object modeledit xla filesupply chain kpi dashboardexcel vba select columnsexcel cell reference vbadouble elimination bracket excelcompare two columns in excel 2007vba loop through worksheetsovertime calculator excel templateconversion stones kilosamortization templatepercent difference calchow to reference blank cell in excel formulanotepad csvexcel macro change cell vbaexternal table is not in the expected formatexcel compare tabslottery syndicate form pdffuel consumption spreadsheetoutlook mail merge attachmentssurety bond calculatorgeneral mail failure quit microsoft excelhow to calculate discounted payback periodhow to make pareto chart in excel 2007how to do carriage return in excelvacation planner for employeesiqy filecreating a gantt chart in excel 2007what is mandayschange minutes to decimalsexcel how to sort multiple columnsvba data structureshow to find empty cell in excelcalculating annual growth rate in exceltable array vlookupexcel workdayvba read text filecustom tournament bracket generatorhow to create a footer in excelhow to create a boxplot in excel 2007how to find duplicate entries in excel 2007wrap text in google sheetsdouble interpolation excelexcel autocorrelation functionvba vbcrmaking an invoice in exceldistance between uk postcodesvba data validationmaskipsleep in vbahow to remove empty columns in excelvba error trapping