I have copied the data from one excel file to another using read range(complete sheet) and white range to waste in the 2nd excel file.
Here a column assume column C has values as text(greencolor marks) and i wanted to convert the Whole column as number.
Because my next step is vlookup using this column
Can anyone please help me on this
@Sarah2 I am a bit confused as to what you are looking for.
Going to take a guess here…
Since you are reading all data from one excel sheet and rewriting it. Initialize a
total variable and use
For Each Row activity to loop through the data table read from the first excel sheet. Place an Assign activty within the loop as the following
Assign total = total + row.Item(excelColumnValue)
@Jarzzz copy and paste is working …i need to convert a particular column values as numbers
Attached is the required activities…i have to the same with uipath …
I have tried to send hot keys but its not working
@Sarah2 It might be easier to use a Macro of sorts… https://support.office.com/en-us/article/quick-start-create-a-macro-741130ca-080d-49f5-9471-1e5fb3d581a8
Can you link an example spread sheet and the flow you have so far?
please find the sample if you are looking Convert General To Number (2).zip (7.6 KB) for the same.
Use a build data table activity to build the data table to support the data you are reading. So have all the columns pre designed. There you can have the column that hold these numbers in a numeric data type. Then use it in the read range to read and hold the data. While reading it will get the values as numbers. Now use the write range using the same data table. It will work for you…
Hi @Sarah2 ,
You can refer below article for do that
I hope this information will be useful for you
Since i have big file (many columns around 30 and 1lakh rows) i think build datatable and getting each row will take time…is ther any other way to acheive this
@Jarzzz Due to confidential data i cant share the file… My flow is
1.copy the data from 1st excel file and write it in the new excel.(3rdfile)
2.copy the data from 2nd excel file and write it inthe 2nd sheet of 3rd file .
3. Now the 3rd file has all the data to do the process.
4.sheet 1 column b I’m using vlookup to get the data from sheet 2(3rd file).
5.while using vlookup some of the rows has the value as #NA and 0 (because the values are formatted as text in the sheet2)
6 . So before vlookup i want to check the sheet 2 column like its in text format or numbers)
7.if its text i want to convert it as a number
8.so that my vlookup formulas will fectch all the values…
@Sarah2 Try using a Send Hotkey Activity with an Alt Key Modifier, and HOE keys being sent.
This will bring you to the Format Cells window in Excel. Use a Click Activity to select Number and then the format you want.
Without having data to work with its hard to test solutions.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.