I have an Excel column named Price that contains a mix of numeric values, text entries, and currency symbols. I need help cleaning the data and calculating the correct total sum of all valid numeric values.

Dataset:

38
abd
389.05
233.92
552.51
122.06
978.63
587.68
600.29
168.34
four hundred
865.77
752.13
411.81
413.13
796.84
N/A
10000
247.82
438.19
300$
523.01
556.93
615.91
N/A
765.69
836.8
336.02
898.69
736.71
N/A
616.63
266.7
343.24
591.53
446.19
696.71
-100
531.06
681.38
588.98
546.04
645.26
826.77
849.07
860.99
705.42
596.15
660.98
896.38
206.19
457.16
233.11
278.9
789.64
40.95
N/A
275.1
933.58
163.16
242.52
209.32
155.76
235.26
587.64
443.13
569.38
593.93
141.49
582.13
741.63
688.68
942.76
351.89
187.48
111.36
530.52
69.48
472.14
868.67
418.38
266.48
538.35
N/A
101.19
730.92
365.34
882.96
504.05
814.68
920.37
881.02
203.63
522.02
944.54
817.46
abd
160.16
497.01
372.28
111.36
645.26

Problem:

• Column contains numbers, text, and currency symbols (like $).
• all numeric values are stored as text (e.g., 300$,all cells).
• Invalid entries like abd, N/A, and four hundred should be ignored and stay as text
• Need to clean the data and ensure only valid numbers are used.

Requirements:

1. Convert values like 300$ and numbers which stored as text into numeric format.
2. Ignore all non-numeric text values just leave them as text.
3. Ensure proper handling of negative numbers (e.g., -100).
4. Compute the correct total sum of all valid numeric entries.
5. Provide the best Excel formula or method for cleaning and summing this dataset efficiently.