How to read only numeric value from an excel column which contains number and alphabets

Help
1

Adding a sum of numeric values of a column, i am getting an error “Input String is not in correct format”

Value in Excel column: 192.00 EA
I need to get only the numeric value 192.00 from it.It works if there is only numeric value but if an alphabet like above ‘EA’ is present it is giving an error

Using: – Works fine if only number is present
" outDataTable.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(outDataTable.Columns(14).ColumnName.ToString()).ToString.Trim) ).ToString"

Tried using regex.replace - Its giving column not present error.

outDataTable.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(System.Text.RegularExpressions.Regex.Replace(outDataTable.Columns(14).ColumnName.ToString(),“[\D]”,“”)).ToString.Trim) ).ToString

Request inputs on the issue…

1 Like
2

Try using IsNumeric method which will let you know if it number or other @mailsmithash

3

Fine
instead we can try split method

" outDataTable.AsEnumerable.Sum(Function(x) Convert.ToDouble(Split(x(outDataTable.Columns(14).ColumnName.ToString()).ToString.Trim)," “)(0) ).ToString”

Cheers @mailsmithash

4

Hi @Palaniyappan

This query is not working

Thanks
Ashwin S

5

Error
Error1022×480 66.8 KB

6

You should pull out just the 192.00 EA and convert it from object to string. This is as simple as pulling it from your datatable as a specific item and appending .ToString at the end.

Then there’s no need to use lambda as that makes it a bit more complicated. Simply use Regex.Match instead. The pattern \d+\.{0,1}\d* should match anything and include the decimal (if it exists). So it would look something like this:

ExcelValueAsString = outDataTable.Rows(0).Item("ColumnName").ToString
ExcelValueAsDecimal = Cdec(Regex.Match(ExcelValueAsString,"\d+\.{0,1}\d*").Value)

7

Hi @mailsmithash

Based on substring you can extract value or

use matches based on parameters

(\d+)

Thanks
Ashwin S

8

I am using it in the Sum function to add all the column values of multiple rows, filtered by a condition in FilterDataTable. The FilterDataTable fetches all the rows of a particular day, so the number of rows is different in each case. Pulling each column value and converting it into String is bit tedious.

9

This is working: Using Split…

outDataTable.AsEnumerable.Sum(Function(x) Convert.ToDouble(Split(x(outDataTable.Columns(14).ColumnName.ToString()).ToString.Trim," ")(0))).ToString

Thanks.

10

Fantastic
Cheers @mailsmithash

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.